0

I am bulk inserting a CSV file into SQL server but after examining the result I realised an issue from the CSV file. The contents of the first name column in the CSV file are comma separated and hence are moved into the next column causing a domino effect.(e.g. Let's say the column names are First Name, Number, and Address. The first Name column has a name called James, Bond. Due to the comma, it is moved to the Number comma and hence the supposed data for Number column is also shifted to the address column. Does anyone have any ideas on how I can rectify this in SQL server.

Improve this question
6
  • possible duplicate of BULK INSERT / OPENROWSET FormatFile Terminator for CSV file with , (comma) in the data Commented Dec 13, 2012 at 15:40
  • Thanx Pondlife...that's exactly the issue i'm facing. But how can i get around it to work for my stored procedure since the excel files i'm bulk inserting come from somewhere else and there are so many of them I can't manually check and rectify it in the excel file. Commented Dec 13, 2012 at 19:12
  • Do you have a CSV file or an Excel spreadsheet? There's no easy way for any program to split CSV data without a valid column delimiter. Maybe you can write a script if you know what data types are expected in each column e.g. if column 2 is always an integer, then Bond, James, 2 must be split as |Bond, James|2 and not as Bond|James|2. But if the data for the first two columns is Bond, James, Blofeld, Ernst then you have a real problem. If the data is in Excel then it's already structured so you can use SSIS or another tool to get the data. Commented Dec 13, 2012 at 20:38
  • yes its a CSV file.and i do know what data types are expected for each column.im currently trying to write a while loop based on the delimiter... Commented Dec 13, 2012 at 20:52
  • The best and easiest solution of all is to fix the source data, so I would also find out if whoever or whatever generates the CSV files can do it with a different delimiter. Commented Dec 13, 2012 at 20:54

1 Answer 1

Reset to default
0

Hi after opening the raw data in notepad, my colleague and I discovered it had text identifiers and hence I basically just used a format file for the bulk insert. Thanx guys for the suggestions.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.