Solved

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

Posted on 2004-09-10
11
304 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
  • 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Server Log File Space 6 34
SQL Connection (Error 18456) 14 36
SQL Script to find duplicates 16 20
Copy Database Wizard Error 3 22
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now