Importing Data from a space delimited text file with column headers in a separate file

We have a large amount of data (4 Gig) to import to a SQL database.  The column headers, data types, and offsets for the space delimitations are in a different file.

If we use the SQL GUI Import Tool we would have to manually set each delimiter.  There are about 450 columns in the database so this would be extremely time consuming and tedious.  I'd like to know if it is possible to do the insert at a command line, maybe with query analyzer, or another way.

The second file with the column headers and stuff looks like the following:
         05 Column1                           PIC X(10)      NOTE OFFSET  0.
         05 Column2                           PIC X(10)      NOTE OFFSET  10.
         05 Column3                           PIC X(10)      NOTE OFFSET  20.
         05 Column4                           PIC X(10)      NOTE OFFSET  30.
         05 Column5                           PIC X(10)      NOTE OFFSET  40.

Each of these first few columns are all 10 characters in lenght, but later on, they change from anywhere 1 to 20.
LVL 16
robrandonAsked:
Who is Participating?
 
ShogunWadeCommented:
Part 3:

modifying this query


SELECT 'CREATE TABLE MyData('
UNION ALL
select RTRIM(SUBSTRING(MyCol,CHARINDEX('column',MyCol),10)) + ' VARCHAR(' + SUBSTRING(MyCol,CHARINDEX('(',MyCol)+1,CHARINDEX(')',MyCol)-CHARINDEX('(',MyCol)-1) + ')' from MyFormatTable
UNION ALL
SELECT ')'

will return a create statement for your table.   which cut & paste into a new QA window & execute.   This will create your table for data then its the bulk insert task
0
 
ShogunWadeCommented:
seems to me that if you can reformat your file that has the structure in you would be able to make it into a format file and use BULK INSERT

or if there are no spaces in the actual data then simply use bulk insert specifying the column delimiter as a space,

eg:

BULK INSERT MtTable FROM 'c:\myfile.txt' WITH ( FIELDTERMINATOR=' ')
0
 
robrandonAuthor Commented:
I'll see about having spaces in the data fields.  How would we go about creating the table to begin with from the file with the column names?

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
robrandonAuthor Commented:
There are spaces within some of the fields, so we cannot use the space as a delimiter.  We need to use the width of the columns.  I found out they range from 1 up to 40, not just 20 in my original post.

0
 
ShogunWadeCommented:
is the bit in brackes in your second file the field length ?
0
 
ShogunWadeCommented:
"1 up to 40,"  that could be nasty.  

why?.....
a row in sql server can only be 8060 bytes (1 page)  i think before you continue yo need to be certain that you are not going to exceed this.
0
 
robrandonAuthor Commented:
ShogunWade,

The last offset column I have is 3250, with a lenght of 3.  So the last character would be at 3253.  I'm not sure about how many bytes that would be taking up....



0
 
ShogunWadeCommented:
thats ok.   i just did the sums in worst case but looks like your ok.


anyway back to the issue then,   how to create the empty table in the first place

what i would in this situation is this  create a table with a single column say varchar(200)   then bulk insert your file containing format info into it

like this :

BULK INSERT MyFormatTable FROM 'c:\format.txt' WITH(COLUMNDELIMITER='~')

'~' -because you want a single row and so use a char thats not in your file.
take a shot at that @ ill post the next bit
0
 
ShogunWadeCommented:
Part 2:

having got this into a table we are going to use the data in the table to construct our CREATE TABLE statement.

the first bit is to make sure that you can parse the format file table properly

try this query:

select RTRIM(SUBSTRING(MyCol,CHARINDEX('column',MyCol),10)) ,SUBSTRING(MyCol,CHARINDEX('(',MyCol)+1,CHARINDEX(')',MyCol)-CHARINDEX('(',MyCol)-1)
from MyFormatTable


This should return 2 columns, the first containing your column name (eg column1 column2 etc)  the second the length of the column.



0
 
robrandonAuthor Commented:
All set!  Thanks!
0
 
ShogunWadeCommented:
Just back from my holidays.   Glad that that sorted the problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.