| Author |
Topic |
|
Beginer2012
Starting Member
45 Posts |
 Posted - 2012-07-10 : 11:02:10
|
| Hello,I'm using BULK INSERT to laod a comma delimited file into a SQL table. I only need a number of columns and want to ignore the rest.Any clues how to do that ?Thank you |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-07-10 : 11:04:51
|
| bulk insert the entire table into a "staging" table. Then use t-sql to pull the data you want into your real table(s)Be One with the OptimizerTG |
 |
|
|
Beginer2012
Starting Member
45 Posts |
Posted - 2012-07-10 : 11:13:45
|
Hello,do I need to know the name of the columns at the 'staging table'?Do you have an example on how to do that ?Thank youquote:
Originally posted by TG
bulk insert the entire table into a "staging" table. Then use t-sql to pull the data you want into your real table(s)Be One with the OptimizerTG
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-10 : 12:30:53
|
| the staging table should have exact number of columns as in your file with same or compatible datatypes------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-07-10 : 13:17:17
|
| Unless the data comes from a consistent and reliable source I would make the all the columns of the staging something like nvarchar(max). That way you are pretty much guaranteed not to have any failures in the bulk insert. Then you have complete control pulling, cleaning, deduping, logging problems, etc from the staging table.Be One with the OptimizerTG |
|
|
|
Beginer2012
Starting Member
45 Posts |
Posted - 2012-07-10 : 14:33:15
|
The problem is I do not know the total number of columns in the file. But I only need the first 25 and want to ignore the rest.quote:
Originally posted by TG
Unless the data comes from a consistent and reliable source I would make the all the columns of the staging something like nvarchar(max). That way you are pretty much guaranteed not to have any failures in the bulk insert. Then you have complete control pulling, cleaning, deduping, logging problems, etc from the staging table.Be One with the OptimizerTG
|
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-07-10 : 14:38:00
|
| Why don't you know the total number of columns in the file? Can't you look at it in some text editor?Do the number of columns in the file change? If so you will need to manually create the staging table each time you get a file.Be One with the OptimizerTG |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-10 : 14:51:21
|
quote:
Originally posted by Beginer2012
The problem is I do not know the total number of columns in the file. But I only need the first 25 and want to ignore the rest.quote:
Originally posted by TG
Unless the data comes from a consistent and reliable source I would make the all the columns of the staging something like nvarchar(max). That way you are pretty much guaranteed not to have any failures in the bulk insert. Then you have complete control pulling, cleaning, deduping, logging problems, etc from the staging table.Be One with the OptimizerTG
is the file delimited or is it fixed length?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Beginer2012
Starting Member
45 Posts |
Posted - 2012-07-11 : 08:40:24
|
It is comma delimited file, and the number columns varies from one file to another...I do not know ahead of time the number of columns.I need to automate the process.Thank youquote:
Originally posted by visakh16
quote:
Originally posted by Beginer2012
The problem is I do not know the total number of columns in the file. But I only need the first 25 and want to ignore the rest.quote:
Originally posted by TG
Unless the data comes from a consistent and reliable source I would make the all the columns of the staging something like nvarchar(max). That way you are pretty much guaranteed not to have any failures in the bulk insert. Then you have complete control pulling, cleaning, deduping, logging problems, etc from the staging table.Be One with the OptimizerTG
is the file delimited or is it fixed length?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
Beginer2012
Starting Member
45 Posts |
Posted - 2012-07-11 : 08:41:23
|
It is comma delimited file, and the number columns varies from one file to another...I do not know ahead of time the number of columns.I need to automate the process.Thank youquote:
Originally posted by visakh16
quote:
Originally posted by Beginer2012
The problem is I do not know the total number of columns in the file. But I only need the first 25 and want to ignore the rest.quote:
Originally posted by TG
Unless the data comes from a consistent and reliable source I would make the all the columns of the staging something like nvarchar(max). That way you are pretty much guaranteed not to have any failures in the bulk insert. Then you have complete control pulling, cleaning, deduping, logging problems, etc from the staging table.Be One with the OptimizerTG
is the file delimited or is it fixed length?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
Beginer2012
Starting Member
45 Posts |
Posted - 2012-07-11 : 08:42:54
|
| Is there anyway to do automate this process ? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-11 : 10:20:49
|
| yep. create a table with very long varchar field (varchar(max)). dump the entire row contents from the file onto this field. then you can make use of string parsing function like below to get the corresponding fields out from it and populate your tablehttp://visakhm.blogspot.com/2010/02/parsing-delimited-string.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-07-11 : 10:32:15
|
| I think -You should be able to do that by using a FORMAT file with any of (bcp, bulk insert, or openrowset). I've never done it but according to the documentation you can "skip" file fields simply by not specifying them in the format file.another alternative to the in-sql parsing option from visakh:If the file fields you need change position then I would probably use some non-sql based process (like .net) to take an input file and restructure it to match your staging table. That may provide its own challenges depending on how diverse the input files can be.Be One with the OptimizerTG |
|
|
|
Beginer2012
Starting Member
45 Posts |
Posted - 2012-07-12 : 14:06:55
|
| I want to thank you all for all the hints and tips. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-07-12 : 15:06:42
|
| you're very welcome - let us know what solution you end up withBe One with the OptimizerTG |
|
|
|
|
BULK INSERT