Solved

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

Posted on 2004-09-10
11
309 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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
efficient backup report for SQL Server 13 81
connection to SQL 2012 error in windows 10 18 48
SQL Convert rows to columns 5 34
Sorting a SQL script 5 41
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

739 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