Linux Script Extract Information from Excel to Create Users

Extracting Excel to text on Linux server

It's not particularly elegant, but you might try using the Linux command "strings" to extract just the printable characters from your spreadsheet file first. Then you could parse the output until you see the column headings, and the data should be after that.

How can I extract a particular worksheet in an xlsx spreadsheet as a text file?

The following snippet might give you a starting point.

You will probably have to find the correct path of the Interop dll on your hardrive and then adjust the add-type cmdlet.

$dir is the directory from which the workbooks are opened and into which they're written.

$dir = convert-path ~/ZZZ/Excel/Export-CSV/

add-type -path 'C:\Program Files (x86)\Microsoft Office\Office16\DCF\Microsoft.Office.Interop.Excel.dll'

$xls = new-object Microsoft.Office.Interop.Excel.ApplicationClass
$xls.visible       = $true
$xls.displayAlerts = $false

foreach ($wbFile in get-childItem $dir\*.xls*) {
   $wb = $xls.workbooks.open($wbFile.fullName)

   try {
      $sh = $wb.sheets('Approval_Logs')
   }
   catch {
      if ($_.exception.message -match 'Invalid index.') {
         write-host "Expected sheet not found in $($wb.name)"
         $wb.close()
         continue
      }
      throw $_
   }

   $sh.select()

   $csvFile = "${dir}$($wbFile.basename).csv"
   $wb.saveAs($csvFile, 6, $false)
   write-host "$csvFile was saved"

   $wb.close()

}

$xls.quit()

How to extract values from a text file using a script?

  string[] lines = System.IO.File.ReadAllLines(@"C:\Users\Public\TestFolder\WriteLines2.txt");

  foreach (string line in lines)
        {
           String[] Contain=line.Split(",");
             foreach (string ordata in Contain)
             {
                  String[] data=ordata.Split("=");
                  var Value=data[1];
               // Write Code for store Data
              }
        }

above code helps you to make it working.

How to extract desired pattern from file name using bash?

To exclude everything after the final dash, use something like base=${file%-*}

for f in davinci_final_240/*.log
do
  file=${f##*/}
  echo "${file%-*}"
done

How to extract data from Excel and output in txt file along with modified string using Python?

This is the type of question that's a little difficult to answer, because I'm afraid the answer is more or less just "go and learn how to actually program in Python". It seems like you're somewhat just blindly copying code you see in tutorials - if you were fluent in Python it would be obvious how to do what you're trying to do and why what you're doing right now won't work.

Still, since solving many individual problems is one of the way you learn something, let me see if I can give you some pointers here.

Your code currently looks like this (note that I shortened the output file path for the sake of readability in this answer):

for i in range(6, ws.max_row+1):  
    name = ws.cell(row=i, column=1).value
    outputFile = open('{}.txt'.format(name), 'w')  
    for j in range(1, ws.max_column + 1): 
        outputFile.write(ws.cell(row=i, column=j).value + '\n')  
outputFile.close()

I can't run this code because I don't have any Excel files to hand, but I imagine it should produce four files called Name.txt, Salary.txt, Date.txt, and Phone.txt. Each file should contain the values from the corresponding row of the worksheet, separated by newlines.

Your questions are: (1) why is this outputting to four files instead of one, and (2) how can you write the SQL commands you want to that file instead of just the values from the worksheet.

For (1), the script is writing four files because that's exactly what you're telling it to do. You call open() four times, with four different filenames, so it creates four files. If you want to create just one file and write to that, try something like:

outputFile = open('output.txt', 'w')  
for i in range(6, ws.max_row+1):  
    name = ws.cell(row=i, column=1).value
    for j in range(1, ws.max_column + 1): 
        outputFile.write(ws.cell(row=i, column=j).value + '\n')  
outputFile.close()

To write the output that you want, you should... write the output that you want. For example, to write the line "CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE(" to the file, you write

outputFile.write("CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE(" + "\n")

To write "Hello", you run outputFile.write("Hello"), and so on. ws.cell(row=i, column=j).value gets you the contents of the (i, j)-th cell in the worksheet, which is why passing it to write() causes that value to be written to the file. Just call write() with what you want to be written to the file.

---

Related Topics