INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Joining 3 CSV files into one

Joining 3 CSV files into one

(OP)
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




RE: Joining 3 CSV files into one

(OP)
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

RE: Joining 3 CSV files into one

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

RE: Joining 3 CSV files into one

(OP)
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

RE: Joining 3 CSV files into one

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Joining 3 CSV files into one

(OP)
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


RE: Joining 3 CSV files into one

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Joining 3 CSV files into one

(OP)
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?

RE: Joining 3 CSV files into one

(OP)
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

RE: Joining 3 CSV files into one

(OP)
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

RE: Joining 3 CSV files into one

(OP)
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.

RE: Joining 3 CSV files into one

(OP)
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

RE: Joining 3 CSV files into one

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

Thanks
Michael

RE: Joining 3 CSV files into one

(OP)
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

RE: Joining 3 CSV files into one

(OP)
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

RE: Joining 3 CSV files into one

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

RE: Joining 3 CSV files into one

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

Thanks

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close