Importing Flat File - which Code page and Which Delimiter?

SQL Server Administration
1

I have an ugly .xlsx file to import on a 1 time basis. I'm using the Import/Export Wizard as it is an ad-hoc request.

The default coed page 1252 ANSI latin and Delimiter Delimited isn't working
I tried Delimiter Fixed Width and that resulted in only 1 column being generated.

How can I determine which code page and delimiter to use ? Thanks

2

Rather than struggle, can you export the data to CSV or tab-delimited and just import that into SQL?

1 Like
3

Ah thanks for jogging my memory @robert_volk. I've used that technique before.
Now I'm getting closer.
The error now is "The given value of type String from the source cannot be converted to type nvarchar for the specified column. "
And below that get :"Data would be truncated"
Is there any way to determine WHICH column has the data type conversion issue? (The destination table will have 100 columns or so.)
I have increased the lengths of the most likely from nvarchar 50 to nvarchar 500 but it still fails.
Thanks

4

I was going to suggest not using SSIS at all, save it to plain CSV/TSV and use BULK INSERT or bcp to import it. Those should give you more specific error messages, and you can easily divert error rows to another file. I know SSIS can divert error rows too.

2 Likes
5

I was able to finally get the 31 k rows imported, @robert_volk. Had to change several column types to varchar(500) and allow nulls.
Thanks for pointing me in the right direction in your first reply.