Importing a DAT file
-
Hi,
I have to import (ETL) a DAT file into one of our SQL databases but I'm having trouble getting the columns to break up and include the headers. I've tried pipe, comma, tab, etc but no luck. Below is the layout I was given. Is there a way to ETL a DAT file or is there a middle step that needs to happen before importing it.
-
DAT files don't normally have any type of conformed format; they're normally specific for the application they are used by.
Could you provide a sample copy of one of your DAT files? Then we'll know what it looks like and can work out a way to interact with it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - BI_Developer - Tuesday, October 3, 2017 8:54 AMHi,
I have to import (ETL) a DAT file into one of our SQL databases but I'm having trouble getting the columns to break up and include the headers. I've tried pipe, comma, tab, etc but no luck. Below is the layout I was given. Is there a way to ETL a DAT file or is there a middle step that needs to happen before importing it.
It's not delimited so you need to define the format as fixed width.
For row width you would use 87 (the sum of the column lengths)
Add six columns and then define each columns size for the input.Sue
- Sue_H - Tuesday, October 3, 2017 10:27 AMBI_Developer - Tuesday, October 3, 2017 8:54 AMHi,
I have to import (ETL) a DAT file into one of our SQL databases but I'm having trouble getting the columns to break up and include the headers. I've tried pipe, comma, tab, etc but no luck. Below is the layout I was given. Is there a way to ETL a DAT file or is there a middle step that needs to happen before importing it.
It's not delimited so you need to define the format as fixed width.
For row width you would use 87 (the sum of the column lengths)
Add six columns and then define each columns size for the input.Sue
Thanks Sue, I followed your recommendation but when looking at the values and preview, it doesn't look correct. Please see below for both screenshots. Could you tell what's off?
-
Either the spec you were given is wrong, the column lengths you have defined are wrong or the file isn't formatted correctly. That's why the values seem to be sliding over. Is the file purely fixed width or is it line delimited?
-
No idea as I can't see the file.
It looks like it could be the EOL used in the file vs what you defined . I use Notepad ++ where you can view the end of line characters.The file is going to be fixed width or delimited. I used to get dat files that were fixed width. Usually you should be able to look at the file and be able to tell if it's fixed or delimited. If you open up the files in a text editor, most have a way to view line numbers, see character positions, etc. You can do that to verify what you were given for column sizes. Not sure what you are using for a text editor but try looking at your file with one other than Notepad.
Sue
-
Sue,
I opened it in Note++ and indeed was able to better make out the columns and their length, so I went back to ETL and this time I selected "ragged right", then added column names and VOILA! it imported successfully.Thanks all for your help and suggestions.
- BI_Developer - Tuesday, October 3, 2017 12:58 PM
You are very welcome. Thanks for posting back what worked - that always helps others with the same problems.
-
I fully agree. Just as FYI, after selecting "ragged right" you still need to adjust you field lengths and renaming the field names.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply