Solved

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

Posted on 2004-09-10
11
308 Views
Last Modified: 2006-11-17
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.
0
Comment
Question by:robrandon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12028251
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
 
LVL 16

Author Comment

by:robrandon
ID: 12028367
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
 
LVL 16

Author Comment

by:robrandon
ID: 12028424
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:ShogunWade
ID: 12028460
is the bit in brackes in your second file the field length ?
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12028481
"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
 
LVL 16

Author Comment

by:robrandon
ID: 12028651
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12028718
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12028838
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
 
LVL 18

Accepted Solution

by:
ShogunWade earned 500 total points
ID: 12028955
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
 
LVL 16

Author Comment

by:robrandon
ID: 12084549
All set!  Thanks!
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12131844
Just back from my holidays.   Glad that that sorted the problem.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SqlServer Table Triggers 3 28
MSSQL join different row from other table 14 65
How can I find this data? 3 23
ODBC settings not showng in FileMaker External Data Sources 6 20
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question