0

I have a situation in which our client has given a flat file (.csv) to be loaded into a SQL Server table using SSIS.

This huge raw .csv file (with about 400,000 records) does not have a proper csv format - particularly it does not have the double quotation ("), either in the column name header or in the row data. Please see an example below: 

ColumnName 1, ColumnName 2, ColumnName 3,....

Z ,  342343242424  , 10.83  ,  000001001765  , 2018-12-03 , 2018-12-03 ,  ABCD  ,  **XYZ,**  ,  CA ,  001  ,  00  , ?

(This is an instance of a data row, with the column header names as Column 1, Column 2,...There is no double quotation(") at all in this file with extension .csv)

I am using Visual Studio 2017 to work in SSIS.

As you can see the data value XYZ, is desired as XYZ, in a single cell; instead I see XYZ in one cell and a blank in the adjacent cell to the right. (I am currently de-bugging using a Data Viewer.)

Every cell gets shifted one place to the right side. In the last column on the right side, I get this: 00 , ? in one cell.

In the SSIS Flat File Connection Manager Editor - General page, I have set the following properties:

Format: Delimited
Text Qualifier:    none
Header row delimiter:  {CR}{LF}

I do not have an idea on how to load this sort of an improperly formatted .csv file (without double quotations(")).

Is there a way I can load properly ? May be changing the file to a text file or something.

I have been told that these are Cobol (Mainframe) files with fixed specific width for each column.

There is also a specific width for each filler (i.e. blank space (including comma) between adjacent values). I have been given a specification document with information such as this below:

Field Name           Start          Width
-----------------------------------------    
ColumnName1             1            26
FILLER                  27            5
ColumnName2             32           19
FILLER                  51            5
ColumnName3             56           21
FILLER                  77            5
.
.
.
.

Any suggestion will be appreciated.

user3812887
  • 439
  • 12
  • 33
  • 2
    SSIS supports fixed with CSVs as far as I remember. Try checking this question out: https://stackoverflow.com/questions/10289640/how-to-import-a-fixed-width-flat-file-into-database-using-ssis I think it's exactly what you're looking for. – Evaldas Buinauskas Dec 28 '18 at 22:23
  • In the SSIS Flat File Connection Manager Editor - General page, when I choose the following : Format: Fixed Width, I am not seeing all the columns from the source .csv file. I am seeing just one column called Column0 in the Advanced tab. In the Columns tab, even after adjusting the row width, I see only one column. Am I doing something wrong to use the fixed width property ? – user3812887 Dec 28 '18 at 22:50
  • 3
    You probably have to declare these columns yourself, together with their lengths. – Evaldas Buinauskas Dec 28 '18 at 22:53
  • OK, will get back with the result on Monday. Thanks Evaldas. – user3812887 Dec 29 '18 at 01:26
  • @EvaldasBuinauskas Is there a way to add the columns manually either in the Flat File connection manager or the Flat File source in the SSIS Data Flow ? I think SSIS does the sensing of columns automatically, right ? (I am yet to try what you have mentioned from my end, but just wanted to get it clear whether you have any specific strategy to do this). I will re-start the work on Monday. But feel free to comment if you have something in mind – user3812887 Dec 29 '18 at 18:28
  • @EvaldasBuinauskas In SSRS, I know we can manually add columns that the dataset columns does not spot automatically. – user3812887 Dec 29 '18 at 18:29
  • I'll be able to check that myself next year chap. Is sample data in your question correct? – Evaldas Buinauskas Dec 29 '18 at 18:36
  • @EvaldasBuinauskas The data sample is correct. But **XYZ,** is actually XYZ followed by a comma on its own, not any astrix symbol. Somehow the editing is not good in stackoverflow, I tried to use Bold. You can have a look here too: https://social.msdn.microsoft.com/Forums/en-US/0dae6f87-c9d9-4f3f-88f2-cba3e54b4f0f/microsoft-ssis-load-a-flat-file-csv-with-multiple-commas-and-no-double-quotations?forum=sqlintegrationservices&prof=required – user3812887 Dec 29 '18 at 18:38
  • I have posted the solution myself in the social msdn link above – user3812887 Dec 31 '18 at 19:14

0 Answers0