4

I have a whole bunch of data files with different content but identical layout. I need to convert them to CSV before importing them into a CRM application.

I usually open the tab-delimited file in Excel, which prompts me to manually define the tab stops using the Text Import Wizard. Once I have the file opened I convert it to CSV.

Problem is I have a bunch of these files (say 20-30) and Excel doesn't remember what one does in the Text Import Wizard and for reasons too lengthy to explain here I can't just combine them all into one mass file - even though the data structure is identical.

I'm wondering if there is a program or process out there that will allow me to save a "template" of the data file structure, and then apply it against each file so I don't have to recreate it manually?

6 Answers 6

3

For this I would normally use a batch process in a scripting language

For example, this is AutoIt:

$getfile = FileOpenDialog("Choose a file",@ScriptDir, "*.tsv",7)
If StringInSTr($getfile,"|") = 0 Then
    $split = StringSplit($getfile,"|")
    For $i = 2 to $split[0]
        $file = FileOpen($split[$i])
        StringReplace($split[$i],@TAB,",")
        FileClose($split[$i])
    Next
Else
    $file = FileOpen($getfile)
    StringReplace($file,@TAB,",")
    FileClose($file)
EndIf

This would be just as easy in VBScript, Batch, Python or Perl.

0

What tools do you have at your disposal? For example, are you limited to Windows GUI, or do you, say, have a Linux shell account? can you donwload arbitrary junk, or are you limited to what's on your workstation out of the box?

If nothing else, what about opening the files up in Word / wordpad / notepad and just doing a global search and replace, substituting tabs with commas? Or maybe with ",", if your data set contains commas.

0

I tend to use Textpad for this kind of stuff as I can do search and replace using regular expressions so I swap out tabs for commas. Maybe a similar approach could help in your scenario

0

If the data columns are separated by one or more tabs each

perl -p -i.bak -e "s/\t+/,/g" filenames

where filenames could be *.tsv for example - or a list of files names.

If the data columns are separated by space characters and none of the actual data includes spaces

perl -p -i.bak -e "s/\s+/,/g" filenames

If the data can contain spaces, I'd write a slightly more complex script using subscript or unpack based on a list of column positions or widths. Post a short example of actual lines of data and desired outcome.

0

If you are on Windows and have PowerShell installed you can use this one-liner to take all .CSV files in the current folder, import them as a "tab delimited" (`t) file, and the export them back out as actual CSVs with the same name, but prefixed with "New-":

(get-childitem "*.csv" | foreach ($_) { ($newName="New-"+$_.name) -and (Import-Csv $_.fullname -Delimiter "`t" | Export-Csv .\$newName -notype) }) -and (Remove-Variable "newName")

For clarification/scripting sake, here it is busted out into multiple lines:

get-childitem "*.csv" | foreach ($_) { 
    $newName="New-"+$_.name
    Import-Csv $_.fullname -Delimiter "`t" | Export-Csv .\$newName -notype
    }
Remove-Variable "newName"
-1

Using a macro, you should be able to save the delimiter type from fixed width to csv.

From there it is relatively easy to parse through an entire directory of files, opening them as fixed width and saving them as csv.

You could write a macro that processes all the files in a particular directory this way, saving them to an output directory once they are converted.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .