1

my question: Importing delimited files into SQL server

The point of this question is to say that I do not want to specify the types and sizes for all the columns manaully. There are too many, and it is error prone.

I wished to use the suggest types feature, but it is (apparently) known to not work.

What can I do?

That is my question. Should I edit my question to more strongly reflect that? Is that inheritly unclear?

5

Two things:

1) It's unclear because we don't know what's breaking because we don't know your data. Apparently you don't either. You probably have things that look like numbers and text in the same column, so it's guessing there's numbers and blows up on text. That has happened before to me.

2) Tough. You're gonna have to know about your data before you import it. That means defining types and everything else. I just recently imported about 60GB of data into my system from about 160 files and procedures to expand the data, so I can say that while defining a bunch of working tables (at least 1 for each file, some needed several cleanup processes, so an additional table per process) is a pain in the ass, it's necessary.

I don't see how manually defining columns is error prone. Open the file in Excel (it does delimited work as well) and see how wide each column is and work with it from there. You're really trying to make this too hard.

Lastly:

I didn't understand from your question that the issue was "apparently suggested types isn't known to work, how can I work around that". But honestly, the way to work around that is to specify the column types ahead of time.

  • Why is it that for a bulk import command it does not need to know the types? – soandos Aug 5 '13 at 19:21
  • do you mean in the bulk import TSQL command or in the window? Because with the bulk insert command you have to define the table ahead of time so it knows each column already. It's a one-off process. – jcolebrand Aug 5 '13 at 19:48

You must log in to answer this question.

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