May 18, 2012 at 9:52 am
Steve Jones - SSC Editor (5/18/2012)
You can run BCP without a format file and it will help you create one, guessing at the columns.
I dont know what BCP is. 🙁
Jeff Moden (5/18/2012)
If the file is a delimited file and is in good and reasonable shape, neither BCP or Bulk Insert need a format file.
the files are not delimited at all. the format is just every so many spaces (or ranges) is a set of data.
May 18, 2012 at 10:13 am
My apologies.
BULK INSERT is a T-SQL command that essentially does what the command line tool, bcp, does.
BCP - http://msdn.microsoft.com/en-us/library/ms162802.aspx
When in doubt, pop open books online and search. The www version is here: http://msdn.microsoft.com/en-us/library/ms130214
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 18, 2012 at 10:20 am
I have used BCP before in several situations and have found it works lightning fast compared to other solutions. You may want to take a look at these documents:
BCP utility
http://msdn.microsoft.com/en-us/library/ms162802.aspx
Creating a format file
http://msdn.microsoft.com/en-us/library/ms191516.aspx
Structure of Non-XML format file
http://msdn.microsoft.com/en-us/library/ms191479.aspx
"El" Jerry.
May 18, 2012 at 10:37 am
slunt01 (5/18/2012)
Steve Jones - SSC Editor (5/18/2012)
You can run BCP without a format file and it will help you create one, guessing at the columns.I dont know what BCP is. 🙁
Jeff Moden (5/18/2012)
If the file is a delimited file and is in good and reasonable shape, neither BCP or Bulk Insert need a format file.the files are not delimited at all. the format is just every so many spaces (or ranges) is a set of data.
They call that "fixed field format" (along with some pet names) and you can either use a format file or you can load it in as a single string and split it. Both are very fast.
--Jeff Moden
May 22, 2012 at 11:21 am
awesome thanks you all! I really wish i had more time to explore these things... its mainly hit or miss.
Ill do some more playing and see what kind of messes i can make.
thanks again.
May 22, 2012 at 11:42 am
They call that "fixed field format" (along with some pet names) and you can either use a format file or you can load it in as a single string and split it. Both are very fast.
[/quote]
Which one is easier? I have been searching on Google for things on format files and they seem fairly difficult.
I havent heard of the "single string and split it" process.
May 22, 2012 at 11:59 am
Here is the link I use: http://msdn.microsoft.com/en-us/library/ms191516.aspx
Jared
CE - Microsoft
May 23, 2012 at 9:02 am
I dunno...I think SSIS is the better tool for this kind of fixed-width import. Using the GUI to map out the columns for the input file (connection) is fairly easy, and it's repeatable, easy to maintain, and gives the OP another skill item for his resume! 🙂
I've done this for dozens of mainframe-based data files with up to 256 columns each. (We were going from an ancient VSAM system to SQL Server 2008 R2...talk about your culture shift.)
YYMV, but I'm adding another vote in the SSIS column...
May 24, 2012 at 8:14 am
Just my opinion, but I think bcp would be simpler for this. The fixed width format file can be generated quickly from a staging table and a novice won't have to worry about spliting.
SSIS is great, but will take more time to set up the fixed file format for 60 columns.
May 24, 2012 at 9:11 am
ACinKC (5/23/2012)
I dunno...I think SSIS is the better tool for this kind of fixed-width import. Using the GUI to map out the columns for the input file (connection) is fairly easy, and it's repeatable, easy to maintain, and gives the OP another skill item for his resume! 🙂I've done this for dozens of mainframe-based data files with up to 256 columns each. (We were going from an ancient VSAM system to SQL Server 2008 R2...talk about your culture shift.)
YYMV, but I'm adding another vote in the SSIS column...
how did you switch sources and have it keep the column set ups?
May 24, 2012 at 9:54 am
Steve Cullen (5/24/2012)
Just my opinion, but I think bcp would be simpler for this. The fixed width format file can be generated quickly from a staging table and a novice won't have to worry about spliting.SSIS is great, but will take more time to set up the fixed file format for 60 columns.
Even with the import wizard?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 24, 2012 at 12:08 pm
Steve Jones - SSC Editor (5/24/2012)
Steve Cullen (5/24/2012)
Just my opinion, but I think bcp would be simpler for this. The fixed width format file can be generated quickly from a staging table and a novice won't have to worry about spliting.SSIS is great, but will take more time to set up the fixed file format for 60 columns.
Even with the import wizard?
If it were delimited, I'd use the import wizard. With fixed length, I'd generate a format file from a staging table.
With a bunch of different files to load with the same format, scripting bcp would be pretty quick. YMMV.
May 24, 2012 at 1:59 pm
slunt01 (5/22/2012)
They call that "fixed field format" (along with some pet names) and you can either use a format file or you can load it in as a single string and split it. Both are very fast.
Which one is easier? I have been searching on Google for things on format files and they seem fairly difficult.
I havent heard of the "single string and split it" process.
[/quote]
There's not much difference in ease because I cheat like hell. I get the people sending me the data to also send the record layout in the form of a spreadsheet. Then, I write a formula to have the spreadsheet either create the format file or build the code for the blob split. The BCP or BULK INSERT with the format file is going to be real tough to beat. The blob split isn't too bad either.
SSIS uses the same engine as BULK INSERT so the speed is probably there but, for me anyway, is much more complicated than setting up a BULK INSERT for the reasons previously stated.
--Jeff Moden
May 25, 2012 at 3:19 am
Jeff Moden (4/29/2012)
Oh, horse muffins! 😛 BULK INSERT with a format file makes this a simple T-SQL task.
Hey, this looks interesting. I've spent quite a few hours creating a SSIS-package because Import Wizard doesn't import null values as null from my source files. I'll immediately explore the KEEPNULLS argument in BULK INSERT.
May 25, 2012 at 8:54 am
Jan.Sundbye (5/25/2012)
Jeff Moden (4/29/2012)
Oh, horse muffins! 😛 BULK INSERT with a format file makes this a simple T-SQL task.Hey, this looks interesting. I've spent quite a few hours creating a SSIS-package because Import Wizard doesn't import null values as null from my source files. I'll immediately explore the KEEPNULLS argument in BULK INSERT.
I don't believe that the KEEPNULLS argument will work in a Fixed Field format because it's spaces that make up any missing data in that format... not nulls. If it's a delimited file, then the KEEPNULLS will work when two delimiters are adjacent to each other.
--Jeff Moden
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply