asked on

Split and merge files

I have a file "tobesplitted.xls" and it has 10 columns(column A, Column B,....) I need to split that in to two files....first file containing only the column A and the second file containing the rest of the columns...

so i should get two files "splittedfilecolumA.xls" and "Splittedfilerestofcolumns".

I also want to merge two files again "splittedfile(containing Column A and Column A1)" with "Splittefilerestofcolumns.xls".

Hope i have made my requirement clear.

Appreciate if this can be done in unix

ASKER CERTIFIED SOLUTION

serialband

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

arnold

Xls, is formatted/encode.
You gave to first the data as a csv as serialband pointed out.

Using perl, you would split each row based on comma.

It is fairly straight forward using perl with nothing files saved as csv data stripping excel formatting coding.

Once saved as csv, using perl you can open each file using a different handle.
Then you can go a line at a time in each splitting each into own array
Then whether you are writing to two, three separate files..
The above presumes that the data is organized .....in a one to one.....

One extract the A1column
Combining the above extracted cell with data from the other file.

Since files are in excel, why not look at vbscript or powershell to access, extract data directly from the excel file.

Ever look at using excel's data extract to pull data from file1 column A for A, and another query that pulls data from file2 to build data in b:n.......
......

tel2

Hi new name,

Questions:

Q1. Does the source file need to be in .xls format, or can it be .csv?
Q2. Would you be OK with a solution which exports the .xls file to .csv, then splits that?
Q3. If so, would do the split files need to be .xls or would .csv be OK? (Excel can open .csv files, anyway.)

'I also want to merge two files again "splittedfile(containing Column A and Column A1)" with "Splittefilerestofcolumns.xls".'
Q4. What do you mean by "Column A1"? I thought A1 is a cell, not a column, in Excel.

Q5. Do you have Perl loaded on your system and are you open to Perl solutions?
Q6. Are you able to install Perl modules
Q7. What flavour of UNIX are you running?
Q8. Please provide test data files as attachments for all 4 files (original file, 2 split files, final merged file) so we can make sure there's no major issues with this.

Thanks.
tel2

FishMonger

Perl has modules for reading and writing excel binary files.

Here are some of the more popular ones.
Spreadsheet::ParseXLSX - parse XLSX files
Spreadsheet::ParseExcel - Read information from an Excel file.
Spreadsheet::ParseExcel::SaveParser - Rewrite an existing Excel file.
Spreadsheet::WriteExcel - Write to a cross-platform Excel binary file.

CPAN search results for Spreadsheet::* modules

tel2

Hi new name,

I see you've accepted serialband's answer.

Did you decide you can work with CSV files instead of XLS files?

tel2