April 5, 2016 at 7:06 am
I have run into an issue with several clients when importing their files into tables. I can manually import by using the Import\Export wizard and hit the correct row count. However, using OpenRowSet and Bulk insert, I come up rows short. Searching down into the millions of lines, I find areas where the field terminator suddenly changes from the default "tab" to just a singular space and then back again. This results in a shorter row count as the bulk insert fills the fields with the next available data. Does anyone know of a way to handle this? I realize that we can build an SSIS package, but the insert needs to be called from a routine. The clients are reluctant to change their exports and are working through Zerion. The code below is what I am executing through a called stored procedure passing @path
Truncate table tblClientData
DECLARE @SQL_BULK VARCHAR(MAX)
Set @SQL_BULKInsert = 'BULK
INSERT tblClientData
FROM '''+ @path +'''
WITH
(
FIELDTERMINATOR = ''\t'',
OWTERMINATOR = '''',
FIRSTROW=2
)'
Exec(@SQL_BULKInsert)
Thank you in advance for your ideas and suggestions
jfh2841
April 5, 2016 at 7:35 am
Are there any 'singular spaces' anywhere else in the data?
If yes, you have a very solid basis on which to complain to the creators of the files – how would any system ever be able to differentiate between 'space character' and 'space delimiter'?
Regardless of the answer, it's very poor form on the part of the file creators and, if it were me, I'd be embarrassed into fixing it.
April 5, 2016 at 9:15 am
Thank you. I had to convince my employers by showing them graphically what the files looked like. They are now contacting the creators.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy