You have some interesting features in your spreadsheet. The column names in the first row have trailing spaces in them. The columns values in rows 2+ have spaces after the closing double quotes (3rd column), and leading spaces for the fourth and final two columns. This smells like a file generated from a mainframe and forced into a CSV.
Were it me, I would not identify this a column delimited file (based on the 2 lines of data). This is a ragged right file format that someone has added text delimiters and column delimiters to.
- Use a LF as the header row delimiter
- header rows to skip = 1
- uncheck column names in first row
In the Columns tab, set a column where the quotes and commas are.
In the Advanced tab, every other column is named with ignore and the next column will be the actual data.
In my data flow, I pulled in the ignore columns to show that they did contain junk but in your package, you would not want to do this.
What's left?
Cleanup.
You can't convert your numbers to a number data type as those leading spaces will cause issues (if memory serves me correctly). Plus, the string data may have trailing spaces as well so I would have a derived column that applies TRIM to all the columns.
From that point, then try and convert to a strongly typed value and land it in your database.
Here's my sample input file (saved with unix/linefeed/LF endings)
"NAME ","OPEDT ","OBJ","PCT","MGR ","rr ","WCASH "
"AAAA","07/12/92","BELL" , 3.23,"AUJ", 0, 12364.00
"BBBB","01/05/91","PELL" , 0.78,"ACC", 0, 9879.00