Import a Fixed Length Datasource text file into SQL Table
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.
and the next 10 is the Vendor Class
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.
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
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?
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.
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?
If you would like to submit an answer or comment, please sign in to the eOne portal.