Converting CSV file to Excel

[Venessa]

Hello,

I have a CSV file and I want to convert it to an Excel spreadsheet.  The first two lines of the file contains the headings and every two lines after that is the data the fits in the columns.  How do I unwrap the data to have the first two lines appear at the top as heading and then unwrap the next two lines of data to line up under the headings?

[Luke_C]

@Venessa Do you have any sample data? My initial reaction is to just try importing the file to excel via the data ribbon, but it sounds like the format might be more complex.

[Venessa]

[danilang]

Hi @Venessa 

While the extension on the file might be csv, it's not actually a csv file.  A csv file has a row defining the column names followed by a series of rows each containing one value for each column defined in the first row.  What you have is text file, probably output by a legacy banking system.    To parse this you need to

1.  Import the the file as csv with \n as the delimiter and "First Row Contains Data" checked.

2.  use filter to remove all lines that start with "+---" as well as any blank lines and lines that might represent totals, summary lines, page breaks, etc.

3.  add a record ID

4.  use a formula tool to mark the even and odd rows with 1 and 2.  Use Mod([RecordID],2)

5.  use a filter to split the rows into all the odd rows and all the even rows.

6.  use a join tool on record position to join the even and the odd rows. rename the new fields [odd] and [even]

7.  Add formula tool with [odd]+[even] to concatenate the 2 fields.  At point you should have a a structure that looks like a CSV file.  One row of column headers followed by multiple rows of values, all delimited by the pipe character "|"

8.  Use a Text to Column tool to split on "|" with as many columns as you need.

9.  Remove all the initial columns with a select tool leaving only the CCC and following columns

10. Use a Dynamic Rename in "Take Field Names from First Row of Data" mode to rename the columns with the values that are in the first row.

11. follow with an Auto Field tool to set the field types

If you can post an actual file as opposed to an image of the file, someone here might be able to help you build this.

Dan

[Venessa]

I cannot post the file but I will work with your solution and see  if this will work.