Forum Discussion

Copper Contributor

Aug 25, 2017

Solved

Identify first row as header from delimited text file.

I am fairly new with spreadsheets but am trying to create a delimited txt file to be imported through a COBOL program. The first line looks something like: Branch|Application|Date ............ ...

excel

SergeiBaklan
Aug 25, 2017
Nothing additional is required. For example, such .txt file
Branch|Application|Date A|ab|20170801 B|CD|20170802
could be imported into the Excel by two ways with results as

However, to transfer the number as 20170801 into correct date few additional simple steps will be required to instruct Excel how to transform. With correct source format (in my case ISO one as yyyy-mm-dd) the importing automatically recognize correct date format

SergeiBaklan

Diamond Contributor

Aug 25, 2017

Hi,

In general nothing additional is required if your data have same delimiter as first row. Modern Get&Transform will convert your data into Excel table; legacy Get Data from text creates the range which you could easily transform into the table.

The point it delimiter is to be the same for all rows, otherwise you need to do some additional transformations.

wporter

Copper Contributor

Aug 25, 2017

The delimiter is the vertical bar or "pipe" symbol "|" and is the same for all rows. I was wondering if I could make a change in my program to create the text file that tells Excel: 1) the first row consists only of column headers and 2) create usage for specific fields like a date in YYYYMMDD format for example.

Thanks for looking at it.

SergeiBaklan
Diamond Contributor
Aug 25, 2017
Nothing additional is required. For example, such .txt file
Branch|Application|Date A|ab|20170801 B|CD|20170802
could be imported into the Excel by two ways with results as

However, to transfer the number as 20170801 into correct date few additional simple steps will be required to instruct Excel how to transform. With correct source format (in my case ISO one as yyyy-mm-dd) the importing automatically recognize correct date format
- wporter
  Copper Contributor
  Aug 25, 2017
  I reran it so dashes were inserted after the year and month--worked great.
  Thanks!