We help IT Professionals succeed at work.

Proper conversion of Excel CSV to TXT file Import to Approach database.

127 Views
Last Modified: 2020-09-25
A file format conversion question.

I use the old Lotus Approach database to manage 3000 new records each week.  I have always received the data in a TXT format, comma delimited.  All the data in the format was listed in a very clear alignment of columns with exact starting points for each column of data.  This TXT file has 15 columns of data for each record.

The supplier of data has changed their policies and only provide the data in Excel CSV format.   My problem is getting the new CSV format to import to the existing Approach database.

I have tired doing a Excel save as "TXT comma delimited" but the TXT document shows with not lined up data fields.  The data is there, but not lined up properly and the import function does not execute properly.

How do i convert the Excel CSV to a TXT comma delimited file with properly lined up data fields for each record?  I've attached a converted TXT showing the data fields not lined up correctly.
stp04-15ph.txt
Comment
Watch Question

David FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Independent of what these people call their data, whatever Excel CSV might mean, the data attached is not CSV data + will not ingest correctly using any type of CSV importer.

You'll have to put this file through a normalizer, which will add "," chars between fields + add double quotes around fields which contain commas, to transform this data to actual/real CSV data.

Pretty simple to do.

Use something like https://metacpan.org/pod/Text::CSV_XS makes writing CSV file a snap.

In the case of your data, you will...

1) Read the data.

2) Break the data on fixed substring boundaries (as all fields look like COBOL data aligned on rigid byte boundaries).

3) Load data into an array of hashes.

4) Output data to a .csv file using the Text::CSV_XS module, or any other language with a similar CSV output module.
CERTIFIED EXPERT

Commented:
Could you please also attach the original "Comma delimited" file? It could clarify a few questions, e.g.

1) You are mentioning 15 columns but the file attached has 21 columns which are TAB delimited. And e.g. the 2nd line has extra TAB character before "9475 PALLADIUM HTS" which shifts all the cells right.
2) The Comma delimited file does not have columns lined up obviously. This is typical for SDF format.
3) How the Comma delimited file handles commas in the text?

You could try Excel Save As Formatted Text (Space Delimited). It allows to "define" columns sizes and it could work for you.
David FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
pcelba makes a good point.

If the data you attached isn't the raw data (you've munged it up into non-CSV format), then attach an original CSV data file.
Dan EckertPC repair for seniors

Author

Commented:
David, Thanks for your response.  I apologize, I'm not very good at database stuff.  I developed a process 20 years ago when all I had was the TXT file properly setup and the imports went very smooth.

Your step by step explanation is very quick, but out of my comfort zone.  Is there anyway we can work on this step by step.?  I really don't understand much of what you are trying to help me with.

Thanks.

Dan
Dan EckertPC repair for seniors

Author

Commented:
Pcelba

Here are 2 files, the original Excel CSV and the correct TXT file I have been getting through a backdoor connection.  The TXT here works on the import.

I have to learn how to do this myself from the CSV, the txt i'm getting I'm not sure will always be available.

I've switched dates on the sample files because of what is quickly available to me.  I appreciate you guys help.  I have to do this every Monday.  New CSV tomorrow AM.

Dan

Dan
stp04-29ph.csv
stp04-29ph-2.txt
Dan EckertPC repair for seniors

Author

Commented:
Another question,  Approach offers the option of using a "ODBC" driver. I have no idea what that is, how to use it.  I do have the original install CD for Lotus office suite that has approach install on it.

Is this a better option?

Dan
Dan EckertPC repair for seniors

Author

Commented:
Here is a jpg of what I'm trying to get to.  I see the uploads to you guys changed the way the txt and the CSV looks when it's opened.

This is a quick screen capture.
04-29-view.JPG
Dan EckertPC repair for seniors

Author

Commented:
Here is one more piece of information that might be helpful to figure this out.
web-stwkly-csv367-y2k-0501-1.doc
CERTIFIED EXPERT

Commented:
It seems my last post disappeared... So posting again: This is a task for programmer. Excel cannot produce the file of the given structure.
Your Comma delimited file has fixed structure because all text fields are right padded by spaces to fixed length.

So you may either ask any programmer to write a short EXE for you or try the converter proposed by David.

The conversion does not seem to be difficult in any programming language. Good programmer should ask how the double quote is encoded in your Comma delimited output file.
David FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Solution seems simple.

Don't use the TXT file, use the CSV file as Lotus surely understands normal CSV format for any imports.
Ben Personick (Previously QCubed)Lead SaaS Infrastructure Engineer
CERTIFIED EXPERT

Commented:
this is a padded flat file, I took a look at the spec and will put together a fox for you soon using a cmd script or powershell script
Ben Personick (Previously QCubed)Lead SaaS Infrastructure Engineer
CERTIFIED EXPERT

Commented:
putting my daughter to sleep, writing this on my phone so, will have to test and fix syntax on computer in a little while

<# ##     2&1>nul
REM Script: ConvertCSVToFlat.cmd
@( 
  SETLOCAL ENABLEDELAYEDEXPANSION
  Echo Off
  SET "_eLvl=0"
  REM Set Terms
  SET "_Terms=A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U"
  REM Set Padding:
  SET "_A=11,L, "
  SET "_B=5,R,0"
  SET "_C=50,L, "
)

Call :Main %*

(
  ENDLOCAL
  EXIT /B %_eLvl%
)

:Main

  FOR /F "Tokens=1-21 delims=," %%A IN ('type "%*"') DO (
    FOR %%a IN (%_Terms%) DO (
       CALL ECHO."%%%%a"
       FOR /F "tokens=2-4 delims==," %%b IN ('SET _%%a') DO (
         SET "_Pad="
         FOR /L %%j IN (1,1,%%b) DO (
           CALL SET "_Pad+=%%~d"
         )
         ECHO."_Pad is !_Pad!"
         IF /I "%%c" EQU "R" (
           CALL SET "_Term=%%%%a!_Pad!"
           CALL SET "_LINE+=,"!_Term:~%%~b,-%%~b!"
         ) ELSE (
            CALL SET "_Term=!_Pad!%%%%a"
            CALL SET "_LINE+=,"!_Term:~-%%~b!"
         )
       )
    )
    Echo.Line="!LINE!"
)

  REM  Alternative:  Call self as Powershell script passing a file as an argument.

REM Powershell.exe powershell –ExecutionPolicy Bypass &{"%~f0" %*}

GOTO :EOF
REM. Powershell Portion follows
#>

##Script: Ps load

Open in new window

Dan EckertPC repair for seniors

Author

Commented:
David, Your work sure looks like it's headed in the right direction.  I don't see settings for Column D thru U.  Let me know if I can do anything to help.

Thanks.

Dan
Dan EckertPC repair for seniors

Author

Commented:
David

A lot is lost in the age difference.  I'm working with a 1999 version of Lotus Approach and trying to import a Excel CSV file made with Office 365.

I've tired the direct import of the raw CSV file and it goes to a option list which I've attached.  I'm wondering if the ODBC driver is the way to go.  

Then my question is which one?  Should I convert the CSV to a an old XLS format?  I'm not familiar with ODBC drivers or how to set them up and use them, especially this 1999 version of Lotus Approach.

Thanks,

Dan

Dan
import-options.JPG
Ben Personick (Previously QCubed)Lead SaaS Infrastructure Engineer
CERTIFIED EXPERT

Commented:
@Dan,

  I am ben, and my script is only partial, I was layong down with my 16 month old writing it on my mobile phone, ai know I need to finiah through U, no worries there, but I also know I  will probably need to fix some issues with the script at the computer as its all from my head and human error and all.

I'm heading over to my computer in a bit
Dan EckertPC repair for seniors

Author

Commented:
Thanks Ben.  Sorry for the miss name.  Again,  I appreciate your help.  Would love to see something like that work.  It's after 12 here.  I'm going to get some sleep.

Dan
Ben Personick (Previously QCubed)Lead SaaS Infrastructure Engineer
CERTIFIED EXPERT

Commented:
@Dan was after 12 for me as well, ended up falling asleep, will look into this more today.

Ben
Ben Personick (Previously QCubed)Lead SaaS Infrastructure Engineer
CERTIFIED EXPERT

Commented:
Storing Progress gotta go to work.  this is a WIP, buit I changed over to PS after I figured out some records contain commas in the terms and PS will just handle that a whole lot easier.

<# ##     2&1>nul
REM Script: ConvertCSVToFlat.cmd
@( 
  SETLOCAL ENABLEDELAYEDEXPANSION
  Echo Off
  SET "_eLvl=0"
  REM CMD Wrapper Calls Powershell so you can Drag and Drop CSV Files.
)

Call :Main %*

(
  ENDLOCAL
  EXIT /B %_eLvl%
)

:Main
    for %%A IN (%*) DO (
        REM  Alternative:  Call self as Powershell script passing a file as an argument.

        REM Powershell.exe powershell –ExecutionPolicy Bypass &{"%~f0" %*}
    )



GOTO :EOF
REM. Powershell Portion follows
#>

##Script: Ps load
 
##$File_Raw=Get-Content -Path "C:\Users\Administrator\Downloads\stp04-29ph.csv"
$FilePath_CSV="C:\Users\Administrator\Downloads\stp04-29ph.csv"
$Header=@("Taxpayer_Number","Outlet_Number","Taxpayer_Name","Taxpayer_Address","Taxpayer_City","Taxpayer_State","Taxpayer_Zip_Code","Taxpayer_County_Code","Taxpayer_Phone_Number","Outlet_Name","Outlet_Address","Outlet_City","Outlet_State","Outlet_Zip_Code","Outlet_County_Code","Outlet_Phone_Number","Permit_Type","Tax_Code","Outlet_NAICS_Code","Permit_Issue_Date","First_Sale_Date")
$FileData_Flat_Padded=
$FileData_CSV=import-csv -Path $FilePath_CSV -header $Header
$FileData_Flat_Padded=@()
$Record=@(
<#    ===========    Record Definition    ===========    #>
<#                                                       #>
<#   Chars Length   PERMIT HOLDER RECORD         Term    #>
<# ------- ------   ---------------------------- ----    #>
<#   1-11      11   Taxpayer Number                 A    #> 11,
<#  14-18       5   Outlet Number                   B    #> 5,
<#  22-71      50   Taxpayer Name                   C    #> 50,
<#  75-114     40   Taxpayer Address                D    #> 40,
<# 118-137     20   Taxpayer City                   E    #> 20,
<# 141-142      2   Taxpayer State                  F    #> 2,
<# 146-150      5   Taxpayer Zip Code               G    #> 5,
<# 154-156      3   Taxpayer County Code            H    #> 3,
<# 160-169     10   Taxpayer Phone Number           I    #> 10,
<# 173-222     50   Outlet Name                     J    #> 50,
<# 226-265     40   Outlet Address                  K    #> 40,
<# 269-288     20   Outlet City                     L    #> 20,
<# 292-293      2   Outlet State                    M    #> 2,
<# 297-301      5   Outlet Zip Code                 N    #> 5,
<# 305-307      3   Outlet County Code              O    #> 3,
<# 311-320     10   Outlet Phone Number             P    #> 10,
<# 324-335     12   Permit Type                     Q    #> 12,
<# 338-339      2   Tax Code                        R    #> 2,
<# 342-347      6   Outlet NAICS Code               S    #> 6,
<# 350-357      8   Permit Issue Date (YYYYMMDD)    T    #> 8,
<# 359-366      8   First Sale Date (YYYYMMDD)      U    #> 8
)
#<$File_Raw | FT

foreach ($Line in $File_Raw) {
    $Line=$line -split ","
    If ($($Line.Count) -ne 21) {
        Write-Warning $("
        =================
        $($Line.Count) Terms Found!
        Expected 21 Terms in the Record!!
        Skipping this Record:
        $Line
        " -replace "`n`t+","`n")
    } ELSE {
        for($j = 0; $j -lt $($Line.Count); $j++){
          "Line $($j): $($Line[$j])"
        }

    }

}#>

foreach ($Record in $File_CSV) {
$Record.Outlet_County_Code
$Record.Taxpayer_Address


}

Open in new window

Ben Personick (Previously QCubed)Lead SaaS Infrastructure Engineer
CERTIFIED EXPERT

Commented:
TADA!  I've completed and tested the script through several goes, and also made it so all you need to do is drag the source files onto the batch (CMD) script. :)



Here is the script saved with a dummy Extension:
ConvertCSVToFlat.cmd_REMOVETHIS

Here is one of my Test Source Files:
stp04-29ph_b.csv

Here is the result of processing that file:
stp04-29ph_b.csv_FLAT.txt

Here is the Script code so you can also just Copy and Paste it into a notepad and save it as "ConvertCSVToFlat.cmd"

<# ## & REM Script: ConvertCSVToFlat.cmd <---Hard-Link---> ConvertCSVToFlat.ps1
	REM Script: ConvertCSVToFlat.cmd
	REM Version: 2.0
	@( 
		SETLOCAL ENABLEDELAYEDEXPANSION
		Echo Off
		SET "_eLvl=0"
		cls
		ECHO.CMD: ===================================
		ECHO.CMD:  Converts a CSV File to a Flat File.
		ECHO.CMD: 
		ECHO.CMD: Usage: 
		ECHO.CMD:     %~nx0 ["File1","File2",...]
		ECHO.CMD: 
		ECHO CMD: Alternative Usage:
		ECHO.CMD:     Select your Files and Drag and Drop onto the CMD Script "%~f0"
		ECHO.CMD: 
		ECHO.CMD: ===================================
		ECHO.
		IF EXIST "%~dpn0.ps1" (
			DEL /Q /F %~dpn0.ps1"
		)
		MKLINK /H "%~dpn0.ps1" "%~f0"
	)

	Call :Main %*

	(
	  ENDLOCAL
	  EXIT /B %_eLvl%
	)

	:Main
	ECHO. CMD: %~n0: Found the Following Arguments and will Process them:
	ECHO. CMD: [%*]
		for %%A IN (%*) DO (
			REM  Alternative:  Call self as Powershell script passing a file as an argument.
			ECHO.
			ECHO.CMD: Processing File %%A
			Powershell.exe -ExecutionPolicy Bypass -File "%~dpn0.ps1" %%A
		)
	PAUSE
	GOTO :EOF
	REM. Powershell Portion follows
#>

## Script: ConvertCSVToFlat.ps1 <---Hard-Link---> ConvertCSVToFlat.cmd
## Powershell Portion Begins

$FilePath_CSV=$args
$Header=@("Taxpayer_Number","Outlet_Number","Taxpayer_Name","Taxpayer_Address","Taxpayer_City","Taxpayer_State","Taxpayer_Zip_Code","Taxpayer_County_Code","Taxpayer_Phone_Number","Outlet_Name","Outlet_Address","Outlet_City","Outlet_State","Outlet_Zip_Code","Outlet_County_Code","Outlet_Phone_Number","Permit_Type","Tax_Code","Outlet_NAICS_Code","Permit_Issue_Date","First_Sale_Date")
$FilePath_Flat_Padded="$($FilePath_CSV)_FLAT.txt"
$FileData_CSV=import-csv -Path $FilePath_CSV -header $Header
$FileData_Flat_Padded=@()
$Record_Padding=@{
    <#    ===========    Record Definition    ===========    #>
    <#                                                       #>
    <#   Chars Length   PERMIT HOLDER RECORD         Term    #>
    <# ------- ------   ---------------------------- ----    #>
    <#   1-11      11   Taxpayer Number                 A    #> "$($Header[0])_Length"=11; "$($Header[0])_RL"="L"; "$($Header[0])_Char"="0";
    <#  14-18       5   Outlet Number                   B    #> "$($Header[1])_Length"=5; "$($Header[1])_RL"="L"; "$($Header[1])_Char"="0";
    <#  22-71      50   Taxpayer Name                   C    #> "$($Header[2])_Length"=50; "$($Header[2])_RL"="R"; "$($Header[2])_Char"=" ";
    <#  75-114     40   Taxpayer Address                D    #> "$($Header[3])_Length"=40; "$($Header[3])_RL"="R"; "$($Header[3])_Char"=" ";
    <# 118-137     20   Taxpayer City                   E    #> "$($Header[4])_Length"=20; "$($Header[4])_RL"="R"; "$($Header[4])_Char"=" ";
    <# 141-142      2   Taxpayer State                  F    #> "$($Header[5])_Length"=2; "$($Header[5])_RL"="R"; "$($Header[5])_Char"=" ";
    <# 146-150      5   Taxpayer Zip Code               G    #> "$($Header[6])_Length"=5; "$($Header[6])_RL"="L"; "$($Header[6])_Char"="0";
    <# 154-156      3   Taxpayer County Code            H    #> "$($Header[7])_Length"=3; "$($Header[7])_RL"="L"; "$($Header[7])_Char"="0";
    <# 160-169     10   Taxpayer Phone Number           I    #> "$($Header[8])_Length"=10; "$($Header[8])_RL"="R"; "$($Header[8])_Char"=" ";
    <# 173-222     50   Outlet Name                     J    #> "$($Header[9])_Length"=50; "$($Header[9])_RL"="R"; "$($Header[9])_Char"=" ";
    <# 226-265     40   Outlet Address                  K    #> "$($Header[10])_Length"=40; "$($Header[10])_RL"="R"; "$($Header[10])_Char"=" ";
    <# 269-288     20   Outlet City                     L    #> "$($Header[11])_Length"=20; "$($Header[11])_RL"="R"; "$($Header[11])_Char"=" ";
    <# 292-293      2   Outlet State                    M    #> "$($Header[12])_Length"=2; "$($Header[12])_RL"="R"; "$($Header[12])_Char"=" ";
    <# 297-301      5   Outlet Zip Code                 N    #> "$($Header[13])_Length"=5; "$($Header[13])_RL"="L"; "$($Header[13])_Char"="0";
    <# 305-307      3   Outlet County Code              O    #> "$($Header[14])_Length"=3; "$($Header[14])_RL"="L"; "$($Header[14])_Char"="0";
    <# 311-320     10   Outlet Phone Number             P    #> "$($Header[15])_Length"=10; "$($Header[15])_RL"="R"; "$($Header[15])_Char"=" ";
    <# 324-335     12   Permit Type                     Q    #> "$($Header[16])_Length"=12; "$($Header[16])_RL"="R"; "$($Header[16])_Char"=" ";
    <# 338-339      2   Tax Code                        R    #> "$($Header[17])_Length"=2; "$($Header[17])_RL"="L"; "$($Header[17])_Char"="0";
    <# 342-347      6   Outlet NAICS Code               S    #> "$($Header[18])_Length"=6; "$($Header[18])_RL"="L"; "$($Header[18])_Char"="0";
    <# 350-357      8   Permit Issue Date (YYYYMMDD)    T    #> "$($Header[19])_Length"=8; "$($Header[19])_RL"="L"; "$($Header[19])_Char"="0";
    <# 359-366      8   First Sale Date (YYYYMMDD)      U    #> "$($Header[20])_Length"=8; "$($Header[20])_RL"="L"; "$($Header[20])_Char"="0";
}

$All_Records_Padded=@()

"
PowerShell: Input CSV From = ""$FilePath_CSV""
PowerShell: Output Flat File As = ""$FilePath_Flat_Padded""
"
# EXIT
$All_Records_Padded = foreach ($Record in $FileData_CSV ) {
    <#.Outlet_County_Code.length
    $Record.Taxpayer_Address.length#>
    $Record_Padded=[PSCustomObject]@{
        <#    ===========     Record Definition     ===========    #>
        <#                                                         #>
        <#   Chars Length   PERMIT HOLDER RECORD         Term  # D #>
        <# ------- ------   ---------------------------- ----  - - #>                
        <#   1-11      11   Taxpayer Number                 A  0 L #> "$($Header[0])"=$(IF ($($Record_Padding."$($Header[0])_RL") -eq "L"){ $($Record.$($Header[0])).PadLeft($($Record_Padding."$($Header[0])_Length"),$($Record_Padding."$($Header[0])_Char")) } else { $($Record.$($Header[0])).PadRight($($Record_Padding."$($Header[0])_Length"),$($Record_Padding."$($Header[0])_Char"))});
        <#  14-18       5   Outlet Number                   B  1 L #> "$($Header[1])"=$(IF ($($Record_Padding."$($Header[1])_RL") -eq "L"){ $($Record.$($Header[1])).PadLeft($($Record_Padding."$($Header[1])_Length"),$($Record_Padding."$($Header[1])_Char")) } else { $($Record.$($Header[1])).PadRight($($Record_Padding."$($Header[1])_Length"),$($Record_Padding."$($Header[1])_Char"))});
        <#  22-71      50   Taxpayer Name                   C  2 R #> "$($Header[2])"=$(IF ($($Record_Padding."$($Header[2])_RL") -eq "L"){ $($Record.$($Header[2])).PadLeft($($Record_Padding."$($Header[2])_Length"),$($Record_Padding."$($Header[2])_Char")) } else { $($Record.$($Header[2])).PadRight($($Record_Padding."$($Header[2])_Length"),$($Record_Padding."$($Header[2])_Char"))});
        <#  75-114     40   Taxpayer Address                D  3 R #> "$($Header[3])"=$(IF ($($Record_Padding."$($Header[3])_RL") -eq "L"){ $($Record.$($Header[3])).PadLeft($($Record_Padding."$($Header[3])_Length"),$($Record_Padding."$($Header[3])_Char")) } else { $($Record.$($Header[3])).PadRight($($Record_Padding."$($Header[3])_Length"),$($Record_Padding."$($Header[3])_Char"))});
        <# 118-137     20   Taxpayer City                   E  4 R #> "$($Header[4])"=$(IF ($($Record_Padding."$($Header[4])_RL") -eq "L"){ $($Record.$($Header[4])).PadLeft($($Record_Padding."$($Header[4])_Length"),$($Record_Padding."$($Header[4])_Char")) } else { $($Record.$($Header[4])).PadRight($($Record_Padding."$($Header[4])_Length"),$($Record_Padding."$($Header[4])_Char"))});
        <# 141-142      2   Taxpayer State                  F  5 R #> "$($Header[5])"=$(IF ($($Record_Padding."$($Header[5])_RL") -eq "L"){ $($Record.$($Header[5])).PadLeft($($Record_Padding."$($Header[5])_Length"),$($Record_Padding."$($Header[5])_Char")) } else { $($Record.$($Header[5])).PadRight($($Record_Padding."$($Header[5])_Length"),$($Record_Padding."$($Header[5])_Char"))});
        <# 146-150      5   Taxpayer Zip Code               G  6 L #> "$($Header[6])"=$(IF ($($Record_Padding."$($Header[6])_RL") -eq "L"){ $($Record.$($Header[6])).PadLeft($($Record_Padding."$($Header[6])_Length"),$($Record_Padding."$($Header[6])_Char")) } else { $($Record.$($Header[6])).PadRight($($Record_Padding."$($Header[6])_Length"),$($Record_Padding."$($Header[6])_Char"))});
        <# 154-156      3   Taxpayer County Code            H  7 L #> "$($Header[7])"=$(IF ($($Record_Padding."$($Header[7])_RL") -eq "L"){ $($Record.$($Header[7])).PadLeft($($Record_Padding."$($Header[7])_Length"),$($Record_Padding."$($Header[7])_Char")) } else { $($Record.$($Header[7])).PadRight($($Record_Padding."$($Header[7])_Length"),$($Record_Padding."$($Header[7])_Char"))});
        <# 160-169     10   Taxpayer Phone Number           I  8 R #> "$($Header[8])"=$(IF ($($Record_Padding."$($Header[8])_RL") -eq "L"){ $($Record.$($Header[8])).PadLeft($($Record_Padding."$($Header[8])_Length"),$($Record_Padding."$($Header[8])_Char")) } else { $($Record.$($Header[8])).PadRight($($Record_Padding."$($Header[8])_Length"),$($Record_Padding."$($Header[8])_Char"))});
        <# 173-222     50   Outlet Name                     J  9 R #> "$($Header[9])"=$(IF ($($Record_Padding."$($Header[9])_RL") -eq "L"){ $($Record.$($Header[9])).PadLeft($($Record_Padding."$($Header[9])_Length"),$($Record_Padding."$($Header[9])_Char")) } else { $($Record.$($Header[9])).PadRight($($Record_Padding."$($Header[9])_Length"),$($Record_Padding."$($Header[9])_Char"))});
        <# 226-265     40   Outlet Address                  K 10 R #> "$($Header[10])"=$(IF ($($Record_Padding."$($Header[10])_RL") -eq "L"){ $($Record.$($Header[10])).PadLeft($($Record_Padding."$($Header[10])_Length"),$($Record_Padding."$($Header[10])_Char")) } else { $($Record.$($Header[10])).PadRight($($Record_Padding."$($Header[10])_Length"),$($Record_Padding."$($Header[10])_Char"))});
        <# 269-288     20   Outlet City                     L 11 R #> "$($Header[11])"=$(IF ($($Record_Padding."$($Header[11])_RL") -eq "L"){ $($Record.$($Header[11])).PadLeft($($Record_Padding."$($Header[11])_Length"),$($Record_Padding."$($Header[11])_Char")) } else { $($Record.$($Header[11])).PadRight($($Record_Padding."$($Header[11])_Length"),$($Record_Padding."$($Header[11])_Char"))});
        <# 292-293      2   Outlet State                    M 12 R #> "$($Header[12])"=$(IF ($($Record_Padding."$($Header[12])_RL") -eq "L"){ $($Record.$($Header[12])).PadLeft($($Record_Padding."$($Header[12])_Length"),$($Record_Padding."$($Header[12])_Char")) } else { $($Record.$($Header[12])).PadRight($($Record_Padding."$($Header[12])_Length"),$($Record_Padding."$($Header[12])_Char"))});
        <# 297-301      5   Outlet Zip Code                 N 13 L #> "$($Header[13])"=$(IF ($($Record_Padding."$($Header[13])_RL") -eq "L"){ $($Record.$($Header[13])).PadLeft($($Record_Padding."$($Header[13])_Length"),$($Record_Padding."$($Header[13])_Char")) } else { $($Record.$($Header[13])).PadRight($($Record_Padding."$($Header[13])_Length"),$($Record_Padding."$($Header[13])_Char"))});
        <# 305-307      3   Outlet County Code              O 14 L #> "$($Header[14])"=$(IF ($($Record_Padding."$($Header[14])_RL") -eq "L"){ $($Record.$($Header[14])).PadLeft($($Record_Padding."$($Header[14])_Length"),$($Record_Padding."$($Header[14])_Char")) } else { $($Record.$($Header[14])).PadRight($($Record_Padding."$($Header[14])_Length"),$($Record_Padding."$($Header[14])_Char"))});
        <# 311-320     10   Outlet Phone Number             P 15 R #> "$($Header[15])"=$(IF ($($Record_Padding."$($Header[15])_RL") -eq "L"){ $($Record.$($Header[15])).PadLeft($($Record_Padding."$($Header[15])_Length"),$($Record_Padding."$($Header[15])_Char")) } else { $($Record.$($Header[15])).PadRight($($Record_Padding."$($Header[15])_Length"),$($Record_Padding."$($Header[15])_Char"))});
        <# 324-335     12   Permit Type                     Q 16 R #> "$($Header[16])"=$(IF ($($Record_Padding."$($Header[16])_RL") -eq "L"){ $($Record.$($Header[16])).PadLeft($($Record_Padding."$($Header[16])_Length"),$($Record_Padding."$($Header[16])_Char")) } else { $($Record.$($Header[16])).PadRight($($Record_Padding."$($Header[16])_Length"),$($Record_Padding."$($Header[16])_Char"))});
        <# 338-339      2   Tax Code                        R 17 L #> "$($Header[17])"=$(IF ($($Record_Padding."$($Header[17])_RL") -eq "L"){ $($Record.$($Header[17])).PadLeft($($Record_Padding."$($Header[17])_Length"),$($Record_Padding."$($Header[17])_Char")) } else { $($Record.$($Header[17])).PadRight($($Record_Padding."$($Header[17])_Length"),$($Record_Padding."$($Header[17])_Char"))});
        <# 342-347      6   Outlet NAICS Code               S 18 L #> "$($Header[18])"=$(IF ($($Record_Padding."$($Header[18])_RL") -eq "L"){ $($Record.$($Header[18])).PadLeft($($Record_Padding."$($Header[18])_Length"),$($Record_Padding."$($Header[18])_Char")) } else { $($Record.$($Header[18])).PadRight($($Record_Padding."$($Header[18])_Length"),$($Record_Padding."$($Header[18])_Char"))});
        <# 350-357      8   Permit Issue Date (YYYYMMDD)    T 19 L #> "$($Header[19])"=$(IF ($($Record_Padding."$($Header[19])_RL") -eq "L"){ $($Record.$($Header[19])).PadLeft($($Record_Padding."$($Header[19])_Length"),$($Record_Padding."$($Header[19])_Char")) } else { $($Record.$($Header[19])).PadRight($($Record_Padding."$($Header[19])_Length"),$($Record_Padding."$($Header[19])_Char"))});
        <# 359-366      8   First Sale Date (YYYYMMDD)      U 20 L #> "$($Header[20])"=$(IF ($($Record_Padding."$($Header[20])_RL") -eq "L"){ $($Record.$($Header[20])).PadLeft($($Record_Padding."$($Header[20])_Length"),$($Record_Padding."$($Header[20])_Char")) } else { $($Record.$($Header[20])).PadRight($($Record_Padding."$($Header[20])_Length"),$($Record_Padding."$($Header[20])_Char"))});
    }
  # " ""$($Record.Outlet_Number)"" Becomes ""$($Record_Padded.Outlet_Number)"" "
  $Record_Padded
}

$All_Records_Padded| convertto-csv -NoTypeInformation  | Select-Object -Skip 1  | out-file $FilePath_Flat_Padded


"Powershell: Results saved to Flat File: $FilePath_Flat_Padded

"

Open in new window


Note:   I had to make a few assumptions on how you might handle the padding for the Dates etc, if those are missing, but you can change which way any field is being padded and what character is used to pad the field by adjusting the info in the first section.

I stored that info there because I think its slightly easier to consider all of the user-editable Variables as being above $All_Records_Padded=@() just in case you were changing something and fat fingered it, you would be able to look at the original variables and copy and paste them in if needed.

The following section does the processing of the file's data making sure each item is passed appropriately, storing it in a variable and then outputting the results into a flat file for you.

One thing which I noticed is that the lines which do use quotes around them in the Flat file seemed arbitrary, so for now all terms have quotes.

Let me know how that goes for you on import.
Dan EckertPC repair for seniors

Author

Commented:
Ben,  Thank you so much.  Your skills are very obvious and much further than anything I can do.  I have tried to follow your instructions and copiend the script to the desktop and renamed to a CMD file.  I've tried to drop the raw Excel CSV file on top and have got the following command response.  And the following results.  Can you tell what I may have done wrong?
CMD-response.JPG
stp11-10ph.csv_FLAT.txt
stp11-10ph.csv
Ben Personick (Previously QCubed)Lead SaaS Infrastructure Engineer
CERTIFIED EXPERT

Commented:
@ DAN Eckert

copiend [sic] the script to the desktop and renamed to a CMD file

The error you showed Contains this Text:
2019-05-08-17_20_49-CMD-response.JPG.png
This indicates that you copied the whole question and put it into the CMD Script, instead of just the portion in the Code Tags (See Below)
2019-05-08-17_23_09-Proper-conversio.png
As an alternative Save THIS FILE (Linked on the next line) to your Desktop:
ConvertCSVToFlat-1-.cmd_REMOVETHIS

Rename this File to:


ConvertCSVToFlat.cmd


Once that is done, stick it in it's own file path somewhere, lets say C:\Converter\ConvertCSVToFlat.cmd and give it a test.

I will test the script with the file you attached as well for good measure.
Ben Personick (Previously QCubed)Lead SaaS Infrastructure Engineer
CERTIFIED EXPERT

Commented:
@Dan Eckert,

 Yup this file is looking food when run through the script, please follow the steps in the last post for your system.

I have attached the results of running this CMD Script Here:
stp11-10ph.csv_FLAT.txt

And you can see the result from the drag and drop (Cmd prompt and File opened in notepad++) here:
2019-05-08-17_34_18-C__Users_personi.png
Dan EckertPC repair for seniors

Author

Commented:
Hi Ben,  Dan here.  Again, thanks for all your help.  I'm stuck at trying to copy your script to a working CMD file.  I've read your instructions several times, tried different approaches and renamed to a CMD file, and when I apply the CSV it does not come out at all.  My last attempt is your suggestion to use the "Select All" link at your first send of the completed script.  I've numbered the attempts consecutively to be sure I'm not redoing or missing something.  I'm sure your script works.  It's me not understanding the code in the script and what I should or should not copy.

On the desktop pic you'll see I'm experimenting with the old Lotus 123.  I do have that working.  It's very labor intensive involving converting an 2019 Excel file to a 1999 Lotus format before importing to the Approach database.  Your approach seem much easier and quicker if I can make it work that would be my preference.
desktop-for.JPG
Ben Personick (Previously QCubed)Lead SaaS Infrastructure Engineer
CERTIFIED EXPERT

Commented:
Hi Dan,

 Nice Doggo Pic on your desktop.

What desktop OS are you running?

I suggested putting the files on your Hard disk under C:\convertCSVToFlat\ just in case It was launching itself wrong.

 Have you tried that?

I've done all of my testing on Windows 2012 R2 and Windows 7, with powershell 5.1.1 and 5.0.

What Version of Powershell are you running?

What exactly are the errors you are getting now?

  Do you have time to do a short live session through team viewer or something?

  If its just a matter of copying the files it would only be a short session.

  I do charge on live, however if you can do all the prep legwork on your side (ie install teamviewer/get it workibg/ grab the code ship it to me along with a phone # to call you at, and have E-E page up in a browser )and if its truely only a matter of copying and pasting the script correctly, then we should be able to get that all done within the free 5 minutes.
Dan EckertPC repair for seniors

Author

Commented:
Ben,  Sorry to be late.  I was away for a family weekend.  I'm doing this work now on a Win 7 SP1.  With a PowerShell Ver. 2.0.  Ultimately I will use this on a Win 10 Home for the weekly work.  I tried a fresh "Select All"  of the script and saved as a Command File.  Not getting a response or an error tonight.  And I do have Team Viewer and use it frequently.  Let me know how and when you would like to connect. Not in a hurry tonight.  maybe one evening this week? Dan
Ben Personick (Previously QCubed)Lead SaaS Infrastructure Engineer
CERTIFIED EXPERT

Commented:
Sure I can make myself available for a bit.  Since it's running Silently now I wonder if I need to run as admin (probably)...
Dan EckertPC repair for seniors

Author

Commented:
Ben,  Dan here.  It's been a busy week.  I'm available for the next few hours on Sat afternoon, if you are.  I'm at my pc now text to 2147695123.
Lead SaaS Infrastructure Engineer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.