ECatsBlog
  • Home
  • Web Dev
  • Reviews
  • Thoughts
  • ASP
  • SQL
  • Browser
  • Mobile
  • Cloud
  • CMS
  • ECommerce
  • App Hacks
  • Fixes
  • Mapping
  • 3D Printing
  • XAML
  • SEO

Categories

  • 3D Printing (1)
  • App Hacks (3)
  • ASP (19)
  • Browser (12)
  • Cloud (10)
  • CMS (7)
  • ECommerce (6)
  • Fixes (2)
  • Mapping (1)
  • Mobile (12)
  • Reviews (70)
  • SEO (9)
  • SQL (12)
  • Thoughts (59)
  • Uncategorized (1)
  • Web Dev (91)
  • XAML (9)

Archives

  • June 2019 (1)
  • January 2019 (175)
  • December 2018 (47)

Commas cause Chaos

When Written: Feb 2011

I am not alone in getting annoyed about incompatibilities between the way different products from the same company deal with data. I came across a particularly annoying problem the other day when having to write an import routine to take some comma separated data and bring it into a SQL database. Normally a simple job with a simple ‘BULK INSERT ‘ type query, but this time it was failing, so I fired up Excel and it imported the data perfectly. Strange I thought, and investigated it further with the excellent Notepad++ (http://notepad-plus-plus.org ) below is a example of the data that was causing the problem:

123,”fred smith,The avenue”,567

Now according to Wiki (http://en.wikipedia.org/wiki/Comma-separated_values) this is a perfectly valid format for CSV files so what was going on? After a lot of trawling various forums it turns out that only Excel reads CSV files correctly, all the SQL import tools fall over with this file. I needed a way of importing this data in code, so in the end I had to write my own CSV parser to get the data in, but why should this be necessary? Shouldn’t a query like:

BULK INSERT CSVTest FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

 

Work? Apparently not! Come on Microsoft let’s fix this, or perhaps it is already in the soon to be released next version of SQL server, I wonder? But I’m not holding my breath.

Article by: Mark Newton
Published in: Mark Newton

  • Previous Access updates2 years ago
  • Next Cool LINQs ?2 years ago

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • IIS8 SMTP setup
  • 3D printing the Ness of Brodgar
  • Which phone is best?
  • Mobile Apps
  • HTML5 mobile app dev – part 5

Recent Comments

  • HP Deskjet 3755 Wireless Printer Setup on Windows 10 phone and HERE Maps – Fixed!
2021 ECatsBlog. Donna Theme powered by WordPress