Restructuring Text file to CSV

gjacobse

I have a Text file that needs to have some restructuring done to it before I can import it.

It is a TAB delimited file of about 100 lines so this isn't a large file, but I could foresee my having to import a much larger file (about 3 years of data).

The structure isn't difficult to read,.. but the issue is the REQDATE or (x)DATE is in a format that Excel doesn't like well

Example:

2015-09-23 12:18:46.000

There is no TAB between the year and time. Would it make more sense to go back to the original database and see if it can export differently?

scottalanmiller

So what would an IDEAL format look like?

scottalanmiller

Would this be good?

2015-09-23,12:18:46.000

gjacobse

More along the lines of this:

2015-09-23	11:48:25.000
2015-09-23[tabbed space here]11:48:25.000

gjacobse

I should add, that this will be a Crystal Reports Export to be done by another party.

not that that should make any difference.

scottalanmiller

So you are wanting a TSV, not a CSV?

scottalanmiller

This is super easy. So assuming that your file is myfile.csv and you are on Linux, just do this...

unexpand myfile.csv > myfile.tsv

Like magic, Linux saves the day.

gjacobse

@scottalanmiller said:

So you are wanting a TSV, not a CSV?

While I suppose technically speaking, yes it would be a TSV - Tab Space Value.

However, TSV is not a Text file option in Excel - Only CSV

scottalanmiller

Excel doesn't know formats, it just knows extensions Windows apps are not very smart.

gjacobse

Also, all the data is [TAB] not [,]

gjacobse

@scottalanmiller said:

Excel doesn't know formats, it just knows extensions Windows apps are not very smart.

I won't argue you there...

JaredBusch

@gjacobse said:

While I suppose technically speaking, yes it would be a TSV - Tab Space Value.

This would be Tab Separated Value

JaredBusch

How are you planning to resatructure this prior to importing?

You say a user is doing this? Then teach them how to handle it in Excel. You will be hard pressed to get a user to do much outside of an office application.

Please be more specific about what you are doing with these files.

who is exporting it and from where.
who is restructuring it
who is opening it in Excel
where is it going after that

scottalanmiller

LOL, right. It's Tab instead of space! The original file is an SSB, Space Separate Values.

gjacobse

@JaredBusch said:

@gjacobse said:

While I suppose technically speaking, yes it would be a TSV - Tab Space Value.

This would be Tab Separated Value

Yea,.. there is that...

scottalanmiller

So anyway, that one command solves the problem nice and easy.

gjacobse

I expect that the 'best course of action' is to have the User rerun the export and adjust accordingly.

However I was hoping to perform a trial run of the import with the data I have, about 100 lines or so. But each row has seven (x)DATE items...

scottalanmiller

@gjacobse said:

I expect that the 'best course of action' is to have the User rerun the export and adjust accordingly.

However I was hoping to perform a trial run of the import with the data I have, about 100 lines or so. But each row has seven (x)DATE items...

So does the command that I provided work? Can you provide full sample data so that we can test?

gjacobse

@scottalanmiller said:

@gjacobse said:

I expect that the 'best course of action' is to have the User rerun the export and adjust accordingly.

However I was hoping to perform a trial run of the import with the data I have, about 100 lines or so. But each row has seven (x)DATE items...

So does the command that I provided work? Can you provide full sample data so that we can test?

My apologies Scott, I missed your Linux command in a page refresh.

Sadly I don't have this file on a Linux system.. Windows only...

scottalanmiller

@gjacobse said:

Sadly I don't have this file on a Linux system.. Windows only...

So copy it, only takes a second.