txt import

Question

txt import

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

sl01 Old Hand Points: 379 More actions · Answer 1

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.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

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

EL Jerry SSCertifiable Points: 7063 More actions · Answer 3

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.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sl01 Old Hand Points: 379 More actions · Answer 5

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.

sl01 Old Hand Points: 379 More actions · Answer 6

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.

Jared SSC Guru Points: 61793 More actions · Answer 7

Here is the link I use: http://msdn.microsoft.com/en-us/library/ms191516.aspx

Jared
CE - Microsoft

ACinAZ SSCommitted Points: 1849 More actions · Answer 8

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...

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 9

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.

Converting oxygen into carbon dioxide, since 1955.

sl01 Old Hand Points: 379 More actions · Answer 10

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?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 11

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?

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 12

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.

Converting oxygen into carbon dioxide, since 1955.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL Surfer '66 SSCertifiable Points: 5161 More actions · Answer 14

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.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 15

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)