How to import a CSV, which is not a CSV file, file into SQL database using PowerShell-CodePudding

I have a txt file that look something like this

Number    Name           #about 4 spaces between
89273428       John      #about 7 spaces between
59273423       Hannah      
95693424       David

I'm trying to upload into my SQL Server Database using PowerShell but I'm not sure how to do it so any suggestion or help would be really appreciated.

I tried to convert to csv file but all the content are merge together in 1 single column so I can't do it like this.

        $CSVImport = Import-CSV $Global:TxtConvertCSV
        ForEach ($CSVLine in $CSVImport) {
            $CSVNumber = $CSVLine.Number.ToUpper()
            $CSVName = $CSVLine.Name.ToUpper()
            $Date = $CurDate

            $query = "INSERT INTO Table (Number, Name, Added_Date) VALUES('$CSVNumber', '$CSVName','$Date');"
            Invoke-Sqlcmd -Query $query 
        }

CodePudding user response：

In order to successfully use the import-csv cmdlet your file must have a reliable delimiter. For example if your file is actually tab delimited then you can use: import-csv -delimiter "`t"

If the file has no delimiter, but uses fixed positions of a known length for each "field" then you can do the following:

Please note this will only work if the file uses a fixed layout. As an example, assume we have a file which contains a number and a name on each row. On each line of the file positions 0 through 7 contain the number. Even if not all numbers have a length of 8, those positions are still reserved for them. Positions 15 through 23 contain the name. As for the numbers, even if each name does not take up all of the positions, they are still reserved for the "name" field. Example file contents:

Number         Name
   12345       John
     333       Brittany
    2222       Jeff 
12345678       Johannes

Since there are 7 unused spaces between the end of the number field and the start of the name field, we will ignore those. You could process this file as follows:

$fileContents = Get-Content Path_to_my_DataFile.txt
#use -skip 1 to ignore the first line, since that line just contains the column headings
$recordsFromFile = $fileContents | Select-Object -Skip 1 -Property @{name = 'number'; expression={$_.substring(0,8).trim()}},
    @{name='name';expression={$_.substring(15,8).trim()}}

When this completes you will have an array of objects, where each item is a PSCustomObject containing the properties "number" and "name". You can confirm that the fields look correct by using out-gridview like this:

$recordsFromFile | out-gridview

You can also convert this into a CSV like this:

$recordsFromFile | convertto-csv -notypeinformation

If the file is not actually fixed width, then the substring(start,length) will likely not work. In that case you can potentially leave out the "length" argument to substring from start position to the end of the line, but that will really only work on the last field of each line. Failing that you would have to resort to pattern matching to actually identify where each "field" begins and ends, processing each line individually.