I'd like a better method to import .csv files into Microsoft SQL Server 2008 R2.

The default import wizard maps everything, by default, to a varchar(50), even when its quite obvious that its a columm of numbers. It also lacks support for normalizing data, or mapping columns into different tables. In short, I need something better.

Your recommendations?

  • StackOverflow would be better... – gbn Feb 11 '11 at 17:39

Are you using SSIS to do the mapping and import?

  • No. After an epic search for SSIS, discovered that its not integrated into my version of Visual Studio 2010 - I have to go back to Visual Studio 2008 to use SSIS!!!!!!!!!! – Contango Feb 13 '11 at 21:14
  • I can sympathize. It's pretty ****ing stupid for Microsoft to push a bogus deadline for VS and break something so heavily touted as a "better together" market campaign. In the meantime, aside from buying something, you can use a script to open CSV and enter it into SQL via ADO or ADO.NET. – Skatterbrainz Feb 16 '11 at 1:02

SSIS does give by far the most flexibility. However, for ad-hoc work it's a bit of a hassle.

Since SSIS and the SSMS Import/export wizard share code you can achieve a lot of what you're looking for without going in to SSIS. For example On the Data Source once you've selected your file fo to the 'Advanced' tab and you should see a button that says "Suggest Types" this will change it from varchar(50) to for example (int), you can also enter the type yourself.

If inserting to an existing table you can choose which fields each goes in to. However I rarely trust a CSV file to be well-formed and so always import in to a temporary /staging table and then do my fix-up and insert all in T-SQL.

  • Thanks - I didn't know you could do the "Suggest Types". That makes things a lot easier. +1 for suggesting a staging table - I've discovered that this step has been absolutely necessary for everything I've done so far. A staging table also allows you to update the format of the .csv files with only minor changes. – Contango Feb 18 '11 at 10:47

SQL Server Import and Export Wizard

SQL Server Import and Export Wizard The SQL Server Import and Export Wizard is the easiest utility to work with. Its interactive GUI provides a simple interface to build and run Integration Services packages. You have already used this tool [...]

for more visit us http://server2008.org/?p=888

  • Thank you very much for your answer, I have voted you up. – Contango Feb 28 '11 at 12:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.