March 2, 2016 at 9:47 am
Hi, I have an Excel data source with names and addresses in it. I tried importing directly from the Excel file into SQL Server but had lots of missing data. It would tell me that the import was successful but upon examining the data, I found that some data was NULL in SQL Server. The import process looked at the first few rows of an Excel column and decided that it should be a date column or a float column and then when later rows did not conform to that decision, it left them as NULL. Very risky because it tells me that the import was successful! Some articles stated that I could add IMEX=1 to the connection string but doing that had no effect at all for me. It still made incorrect decisions about the source data.
Next I tried saving the Excel file to csv and then import the csv into SQL Server. This worked much better except for one issue. The full address field in the source data has LF in it in order to put the city, state, zip on a second line. The flat file represents it with LF as the new line character within a field and CR LF as the new row character. I can see the LF and the CR LF indicated in text editors. SSIS is ignoring the LF character and so putting the full address on the same line in the database.
What is the best solution for getting this Excel data into SQL Server properly?
Thanks for the tips.
Howard
March 2, 2016 at 4:37 pm
it seems that it did maintain the LF character after all. It did not show up in Management Studio grid queries but when I output the query to text, the character was still there and making a new line.
Good enough for me.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply