0

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?

Improve this question
1
  • StackOverflow would be better...
    – gbn
    Feb 11, 2011 at 17:39

3 Answers 3

Reset to default
1

Are you using SSIS to do the mapping and import?

Improve this answer
2
  • 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, 2011 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, 2011 at 1:02
2

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.

Improve this answer
1
  • 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, 2011 at 10:47
1

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

Improve this answer
1
  • Thank you very much for your answer, I have voted you up.
    – Contango
    Feb 28, 2011 at 12:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .