|
05-22-2018, 02:34 PM
|
#1
|
Registered User
Join Date: Feb 2002
Posts: 10,861
|
data file question
I want to append a bunch of csv files into one file.
For example, say I have 10 files each with 10 columns by 100 rows. I want to make a file with 100 columns by 100 rows.
What is the easiest way to do that?
I've written code to make a 10 column by 1,000 row file by appending the 10 files together, but writing a script to append the files to make 100 columns by 100 rows is trickier.
|
|
|
05-22-2018, 03:04 PM
|
#2
|
Join Date: Mar 2001
Location: Reno, NV
Posts: 16,943
|
1. Open Target File. (i.e. file you want to put the 100 races into)
2. For each Source file...
3. For each record in the source file...
4. Write that record to the target file.
OR...
If the files are just csv's...
Create a new file using a batch file and append each file to the target file. This would be amazingly fast.
|
|
|
05-22-2018, 03:58 PM
|
#3
|
Registered User
Join Date: Feb 2002
Posts: 10,861
|
Quote:
Originally Posted by Dave Schwartz
1. Open Target File. (i.e. file you want to put the 100 races into)
2. For each Source file...
3. For each record in the source file...
4. Write that record to the target file.
OR...
If the files are just csv's...
Create a new file using a batch file and append each file to the target file. This would be amazingly fast.
|
Thanks, Dave.
I have written a program that will do the bottom half of your reply -- appending each file onto the end of the previous file to create a 10 column x 100 row file.
What I want to do is append each file in a sideways manner so that the 10 column x 100 row file becomes a 100 column x 100 row file.
You're right that I could read each row of the source file and each row of the target file and then append the source file to the right most column of the target file. I was hoping there was some software or an excel macro that would do this.
Right now, I open the target file into excel. Then I open each source file one at a time and copy and paste the data into the first row, 11th cell of the target file. Then repeat at the 21st cell, etc.
It is soooo sloooow to do it manually -- especially if I have to do it several times when the files change.
|
|
|
05-22-2018, 08:17 PM
|
#4
|
Join Date: Mar 2001
Location: Reno, NV
Posts: 16,943
|
Highnote,
Also, not quite sure that I am getting this exactly right. You want to add 100 COLUMNS for for every race?
Usually people add data as ROWS in a table.
Typically, this would lead to an unmanageable file.
If you'd care to give me a call between 6:30 & 7pm (pacific) tonight, we could discuss this for a few minutes.
775.853.1234
Dave
|
|
|
05-22-2018, 08:33 PM
|
#5
|
Registered User
Join Date: Feb 2002
Posts: 10,861
|
Quote:
Originally Posted by Dave Schwartz
Highnote,
Also, not quite sure that I am getting this exactly right. You want to add 100 COLUMNS for for every race?
Usually people add data as ROWS in a table.
Typically, this would lead to an unmanageable file.
If you'd care to give me a call between 6:30 & 7pm (pacific) tonight, we could discuss this for a few minutes.
775.853.1234
Dave
|
No. Let's say I have ten files already made and each one is 10 columns by 100 rows. I want to join all ten files together into one file that is 100 columns by 100 rows.
I do NOT want to join them together to make one file that is 10 columns by 1,000 rows.
Each column has a unique name.
I was hoping there was an easy way to join them together using Excel that did not require cutting and pasting.
Now that I think about it, I could probably join them together in Access since each column is unique.
I don't like Access because it is such a clunky program. Just opening it is a nuisance.
|
|
|
05-22-2018, 08:45 PM
|
#6
|
Join Date: Mar 2001
Location: Reno, NV
Posts: 16,943
|
Okay, that makes more sense. Sorry, it was my brain that heard 100 x 10 instead of 10 x 100.
I also assume that this is not a one-time thing. If it was, then copy/paste in Excel would be relatively easy, as would the append solution.
So, eventually, I assume that you want more that 100 x 100. Perhaps 10,000 x 100.
(My largest file file is 3.75m x 4,500 so I understand scalability.)
|
|
|
05-22-2018, 08:46 PM
|
#7
|
Registered user
Join Date: Oct 2008
Location: FALIRIKON DELTA
Posts: 4,439
|
Quote:
Originally Posted by highnote
No. Let's say I have ten files already made and each one is 10 columns by 100 rows. I want to join all ten files together into one file that is 100 columns by 100 rows.
I do NOT want to join them together to make one file that is 10 columns by 1,000 rows.
Each column has a unique name.
I was hoping there was an easy way to join them together using Excel that did not require cutting and pasting.
Now that I think about it, I could probably join them together in Access since each column is unique.
I don't like Access because it is such a clunky program. Just opening it is a nuisance.
|
Simply execute the following command lines:
You can pass all the file names to merge:
Code:
paste -d, file1.csv file2.csv > merged.csv
Or if they are following the same mask do this:
Code:
ls file* | xargs paste -d, > merged.csv
__________________
whereof one cannot speak thereof one must be silent
Ludwig Wittgenstein
|
|
|
05-22-2018, 11:44 PM
|
#8
|
Registered User
Join Date: Feb 2002
Posts: 10,861
|
Quote:
Originally Posted by DeltaLover
Simply execute the following command lines:
You can pass all the file names to merge:
Code:
paste -d, file1.csv file2.csv > merged.csv
Or if they are following the same mask do this:
Code:
ls file* | xargs paste -d, > merged.csv
|
Thanks, Delta.
I assume this is a DOS command and run from the command line?
|
|
|
05-23-2018, 12:59 AM
|
#9
|
Registered user
Join Date: Oct 2008
Location: FALIRIKON DELTA
Posts: 4,439
|
Sorry, I did not realize you are using windows; the above commands are for linux. In windows you can do something equivalent using the following command:
grep -e ^a*$ files*.csv > merged.csv
If you do not have grep, you can install it here:
http://gnuwin32.sourceforge.net/packages/grep.htm
__________________
whereof one cannot speak thereof one must be silent
Ludwig Wittgenstein
|
|
|
05-23-2018, 12:01 PM
|
#10
|
dGnr8
Join Date: Aug 2003
Location: Niagara, Ontario
Posts: 3,025
|
I would use an editor that has column mode (like UltraEdit) and copy and paste. You could also add the comma separator between them with a couple of clicks.
__________________
.
The great menace to progress is not ignorance but the illusion of knowledge - Daniel J. Boorstin
The takers get the honey, the givers sing the blues - Robin Trower, Too Rolling Stoned - 1974
|
|
|
05-23-2018, 04:01 PM
|
#11
|
Registered User
Join Date: Feb 2002
Posts: 10,861
|
Quote:
Originally Posted by Red Knave
I would use an editor that has column mode (like UltraEdit) and copy and paste. You could also add the comma separator between them with a couple of clicks.
|
I use editpad lite. I don't know if it has a column editor. I know that you can position the cursor to highlight and cut a column, which is a useful feature.
I will read the docs to see if it allows for the pasting of a column. That would be useful.
|
|
|
05-23-2018, 04:02 PM
|
#12
|
Registered User
Join Date: Feb 2002
Posts: 10,861
|
Quote:
Originally Posted by DeltaLover
Sorry, I did not realize you are using windows; the above commands are for linux. In windows you can do something equivalent using the following command:
grep -e ^a*$ files*.csv > merged.csv
If you do not have grep, you can install it here:
http://gnuwin32.sourceforge.net/packages/grep.htm
|
Thanks again! I will check out grep. Never heard of it.
|
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|