Joining 3 CSV files into one

[Cpreston]

Hi

I am trying to join 3 CSV files into one which will be carried out on a regular basis.

Basically all existing data in the Destination file I want deleted before the new 3 files get imported.

I am having problems getting it working. The 3 files to import are literature, samples and Other and the destination we call All. (all 3 files have same headings)

So far I have got 2 data flows for literature and Samples. these are joined together with the green arrow
Under the Literature data flow I have flat file pointing to Literature csv file and then going to All destination file
Under Samples I have flat file pointing to samples csv file and then going to All destination file.

This is far as I have got. (checking the first 2 imports work before I add the third one)

What is happening currently is it runs ok but is only importing the samples data. I think I may have a setting that says overwrite existing data but cannot find it.

1. Need the file to be emptied or deleted (How do I do)
2. Import only one lot of column headings
3. All 3 files to be imported (appended) into destination

Any ideas or advice please

Thanks

 

[Cpreston]

Hi

Quick update

I now have all 3 files importing in. SO now I need to solve

1. Clear the existing file or data
2. Only import one row of column headings

Any ideas please

Thanks

 

[micang]

Hi,

1.) What is the destination output? Is it going for example to an excel file? Instead of clearing out the file, can you create a new one instead?
2.) Are all the files identical in headings?

Thanks
Michael

 

[Cpreston]

Hi

Another update. I now have it all working except the headers.

I am getting all 3 files now importing into one, however it is bringing in the same headers from each file. I only want one set of headers, any ideas please

Thank

 

[fredericofonseca]

3 input files with headers you say.

first question - will text contents of headers change or is it static?

If static you create the output file with headers on first line (which will create the header) and define the input files also with headers on first line - when reading header line will be ignored so when writing the files to output one only data rows will be added.

if contents is dynamic (but number and type of columns does not change) will it matter which one of the files contains the header to output?

and would your design be better suited with a loop container instead of 1 dataflow per file?
or if not feasible then maybe a single data flow with 3 input files, a union transform and a single output?


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

All the headings are static and will always be the same names. I note your solution but not sure where I would do this.

I have the file destination deleted at the beginning of the process
then have the source files which currently update ok but also add in the headings at the start of each new source file imported. Example below (not real heading and row names of course)

heading 1 heading 2 heading 3
Data Row
Dat row
heading 1 heading 2 heading 3
Data Row
Dat row
heading 1 heading 2 heading 3
Data Row
Dat row

So I think you are saying the destination file should be set with headers on first line but also the same on input files.
Where do I set this please.
I have tried alot of ways today but general attmepts still give me headings but then no data except form first file, I am going aorund in circles.

If you could advise your solution in a little more detail that would be great. i cannot try anything until the morning at work.

Thanks

 

[fredericofonseca]

you do that on the definition of the file connection which I hope you have defined as being delimited. first screen you see when you edit/create a new connection.
On the output file also ensure that it is set with a valid text qualifier if you require the output csv file to be RFC complaint

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Ok yes I have then as delimeted and the text qualifiers on all are "

I have 4 connections, 3 input and then then 1 output one.
Which should be set on which?

 

[Cpreston]

Hi
fredericofonseca I had an email notification you have replied but I came into the site and nothing is there, perhaps a glitch on our email system. if not could you post again if you ahve replied. many thanks

 

[Cpreston]

Sorry I am still having major problems getting the headers to go and just leave one row of header at the top

I either get 3 lots of headers, I have managed to get no headers at all. I am just going round un circles not getting anywhere.

Could you advise a little further please.

Thanks

 

[Cpreston]

Just add me more information this is how things are set up currently

Trex, Sample and Literature

ColumnNamesInFirrstdatarow = True
HeaderowToSkip = 0
Format = Delimeted
HeaderRowDeleimter = {CR}{LF}
TextQulaifier = "



Ouput File ContactsSSIS is set as

ColumnNamesInFirrstdatarow = False
HeaderowToSkip = 0
Format = Delimeted
HeaderRowDeleimter = {CR}{LF}
TextQulaifier = "

If I change the OUput file to
ColumnNamesInFirrstdatarow = False it comes up with 3 lots of headins Column0, Column1, etc...


So I am either getting 3 lots of headings, none at all and then not the names of the columns but 0, 1, 2 etc....

I have tried changing all of the above but getting no where, any ideas please.

 

[Cpreston]

Sorry this bit should have read

If I change the OUput file to
ColumnNamesInFirrstdatarow = TRUE it comes up with 3 lots of headins Column0, Column1, etc...

Thanks

 

[micang]

The way I would do this is import all three to a table then export the table out to one file.

Thanks
Michael

 

[Cpreston]

Yes I think from now that would be the wiser way and the way I will do it in the future.
Being it was nothing to do with SQL I did not think of using a table but I suppose I could have used Access also.
So you would create SQL destination table and then use that as the source for the csv file destination good idea.

I have managed to solve it though but there is no logic how it is solved.

I Have the input files of Trex and Sample set to

ColumnNamesInFirrstdatarow = True

The Literature file is set to false and the output contacts file is also False. I will test some more when my heads stopped banging but5 I think it is ok now

Thanks for everyone's reply's and patience

 

[Cpreston]

However, I have one more quick question if the files have comma's in them is there a way I can get rid of them so they do not effect the import
One of the last input files I had had commas in the address fields and this then did not import the data and stopped it running.

I cleared the comma's out and it then ran no problems. Any ideas on this one p[lease.

Thanks

 

[micang]

The last issue you are having is a data issue - if your supplier is sending a CSV (comma-separated file) then they either need to put quotes (") to qualify text or they don't use a comma as the column delimiter but use something else, example pipe (|) or TAB. If comma is used as the delimiter and you have comma's in the data itself, then the import will fall over because for every comma found, it is going to put a column line.

The only way to fix this is to have the source file exported correctly.

Thanks
Michael

 

[Cpreston]

Yes I believe that to be true also, but they say they cannot, I will get back to them on it.

Thanks