cipriano555
asked on
Trying to populate multple tables from a single source data file.
I am trying to populate a bunch of tables from data that is contained in a single text file. Not all target tables have the same number of columns. The text file has rows of data, with the first piece of data telling which file type (which target table) the subsequent data in the row is for.
See attached file for some sample data.
I tried just reading the source file into a single table, with the intent of parsing it and with t-sql and sending the rows of information to the appropriate target tables. But I couldn't get the import wizard to read the text file. It complained that the data was being truncated. I went in and adjust the field lenghts (it thought there were 3 fields) to the maximum, 255. But to no avail, it still would read it.
Thanks for your help!
Cipirano
Attached file has some data, and below are a few rows:
REC_TYPE_1|1|3.0
REC_TYPE_2|1|20120927 08:49:00|101|234223444|197
REC_TYPE_3||||||||1|13|ADV
REC_TYPE_4|234227744|19990
REC_TYPE_5|FOOD||3|||||JOS
REC_TYPE_6||||JOSEPH|ODYSS
REC_TYPE_6||||JOSEPH|ODYSS
REC_TYPE_6||||JOSEPH|ODYSS
REC_TYPE_7|||||
See attached file for some sample data.
I tried just reading the source file into a single table, with the intent of parsing it and with t-sql and sending the rows of information to the appropriate target tables. But I couldn't get the import wizard to read the text file. It complained that the data was being truncated. I went in and adjust the field lenghts (it thought there were 3 fields) to the maximum, 255. But to no avail, it still would read it.
Thanks for your help!
Cipirano
Attached file has some data, and below are a few rows:
REC_TYPE_1|1|3.0
REC_TYPE_2|1|20120927 08:49:00|101|234223444|197
REC_TYPE_3||||||||1|13|ADV
REC_TYPE_4|234227744|19990
REC_TYPE_5|FOOD||3|||||JOS
REC_TYPE_6||||JOSEPH|ODYSS
REC_TYPE_6||||JOSEPH|ODYSS
REC_TYPE_6||||JOSEPH|ODYSS
REC_TYPE_7|||||
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
guess if I can get it to take the pipe as the delimiter it seems to be perfect.
Absolutely. You can use any delimiter you like.
Absolutely. You can use any delimiter you like.
The trick is to come up with a key to be able to join all these rows after the fact. There are a couple of ways to do that:
1. Use a key common to all the records.
2. If there is no common key, create a surrogate and add it to all the outputs. This could be as simple as an incrementing value (1, 2, 3, ...)
1. Use a key common to all the records.
2. If there is no common key, create a surrogate and add it to all the outputs. This could be as simple as an incrementing value (1, 2, 3, ...)
ASKER
It doesn't read the records in correctly. I specified | as the delimiter. REC_TYPE_1 as you see above has 3 fields. So, it appears to think all records have 3 fields and so, for example, REC_TYPE_3 has tons of |'s in its third field. I hate to do a manual fix but in desperation I inserted a first row that has the maximum number of fields anticipated. It looks like this:
header||||||||||||||||||||
But now I think it is not recogizing the start of a new row correctly. And there are still columns which contain bunches of |s. So it is not always using | as a field delimiter.
Please see attached image.
I think my kludge (puting in a rows of ||||| at top) is not really helping.
SSIS-Not-Recognizing-Next-Record.JPG
header||||||||||||||||||||
But now I think it is not recogizing the start of a new row correctly. And there are still columns which contain bunches of |s. So it is not always using | as a field delimiter.
Please see attached image.
I think my kludge (puting in a rows of ||||| at top) is not really helping.
SSIS-Not-Recognizing-Next-Record.JPG
You need to read up on the Conditional Split Transformation. You are clearly not using it and you will not be able to import your file that way.
If you need a tutorial on how to use it, I will see if I can find something.
If you need a tutorial on how to use it, I will see if I can find something.
1. First Take the Whole file data to temp table in database and iterate each row as you require.
Take each column value in variables and insert to your desire table based on condition.
As Above said you can use Conditional split. Or Condition in Direction.
If @Variable == 1 then This way
else second way...
Take each column value in variables and insert to your desire table based on condition.
As Above said you can use Conditional split. Or Condition in Direction.
If @Variable == 1 then This way
else second way...
ASKER