Skip to content
+1-888-319-3663

COMMUNITY FORUM

Import a Fixed Length Datasource text file into SQL Table

Steve Kondolf asked 5 years ago
Brand new SC user (ver 2015).  We have a legacy system that outputs a fixed length text file (about 1000 rows monthly) that we want to import into a SQL table.  Is this possible using Smartconnect?  I’ve built other mappings using csv or Excel with no issues but I cannot figure out how to perform this with a fixed length text file.  Can anyone provide some suggestions how to accomplish this?
Answers
Patrick Roth Staff answered 5 years ago
two ways that I can think of doing this:
1. import file as-is.  those tab or csv file either way.  now the data comes in as one long field value.
2. Now make calculated fields for each field you need in your mapping.  In the script calc field, you would parse the big string apart to where it needs to be.
so maybe the first 20 characters are your Vendor.
CALC_VENDOR
return _SOURCEFIELD.Substring(0,20)
and the next 10 is the Vendor Class
return _SOURCEFIELD.Substring(19,0)
etc
The other way is to use the ODBC Driver to do this.
choose ODBC Data Source as the source and then the text file.  When it asks you to define the schema.ini, you can choose Fixed as the delimiter.  When you do that, then you define each field and give them names and a position.
Now when you do a preview, you’ll see all of your columns that the ODBC driver parsed for you according to what you told it.
 
Steve Kondolf replied 5 years ago

Thanks for the info Patrick. I was finally able to perform the operation. One thing I realized was the data in the file had no headings – it was just raw data as follows:

026736201702 COMM 609.00
026736201702REGSL 2250.00
026012201702REGSL 2157.00

What I finally ended up doing was add a line at the top of the file (i.e. SOURCEDATA) so I could make it the key field. I could then do the calculated fields and by using DATEPART I was able to chop the data up into the individual fields. I didn’t try the ODBC way as the first way did work.

One final question – is there any way to accomplish this without having to add the header in the file?

Patrick Roth Staff replied 5 years ago

I don’t see why you would need a header. Without it, the field name would just be F1 by default and the entire field would be the key. the exact same result of putting a column header called SOURCEDATA – just a different name.

if you used odbc, you can also rename the columns as you wish – they would default F1, F2, etc but you can name them as you add them.

Tanya Henderson replied 3 years ago

I am attempting the ODBC and I am not following this completely. I am assuming i am still choosing Bulk Data Load for the Data Source Type and the Data Source is where I choose ODBC Connection??? I am not getting the same prompts. Can you step this out more clearly?
Thanks,
Tanya


If you would like to submit an answer or comment, please sign in to the eOne portal.