Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

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.
0
robrandon
Asked:
robrandon
  • 7
  • 4
1 Solution
 
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
 
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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
 
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
 
robrandonAuthor Commented:
All set!  Thanks!
0
 
ShogunWadeCommented:
Just back from my holidays.   Glad that that sorted the problem.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now