Convert xlsx files to csv via automated script?

Question

Hello, we have .xlsx files (not xls) and would like to convert them into csv files (so we can import into SQL Server) via some type of automated script to avoid manually doing each day.

(Tried different methods via Import Wizard and OPENROWSET and got 'MICROSOFT.ACE.OLEDB.XXXXX'  not registered error which looks like Microsoft Access Database Engine needs to be installed which want to avoid installing anything extra.)

I searched around and came across some PowerShell scripts which one mentioned "Install-Module ImportExcel" but got error message. Also when trying from this link https://guimatheus92.medium.com/convert-excel-file-to-csv-from-a-powershell-script-3b998b9e8c2f first script I get following:

Like I mentioned we would like to have some type of daily scheduled automation which converts the xlsx files into csv files (perhaps script file within a SQL Server Agent job?) and avoid any security risks (wondering which buttons of Yes or No or Suspend pushing for this particular job case).

Or if there are other methods beyond PowerShell that doesn't need any extra configurations or installations would be open to.

Thanks in advance.

Answer 1

Hi, techresearch7777777

The PowerShell script you provided is secure.

The -Scope Process parameter ensures that this change affects only the current PowerShell process and does not impact other processes on the system or future PowerShell sessions.

Additionally, this is a very simple script that will not affect the long-term security of the computer.

perhaps script file within a SQL Server Agent job?)

A SQL Server Agent job would need to use SSIS to create an ETL project that imports .xlsx files directly into the database, eliminating the conversion step.

Furthermore, the issue you mentioned with 'MICROSOFT.ACE.OLEDB.XXXXX' not being registered can be easily resolved by installing an 80MB file named accessdatabaseengine_X64.exe.

Best regards,

Mikey Qiao

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it.

Answer 2

When you try to run a script or import a module (like ImportExcel), you might get an error if your current execution policy doesn’t allow it.

You can check your current execution policy by running this command in PowerShell:

Get-ExecutionPolicy

and change the the execution policy to allow the execution of scripts, you can use the Set-ExecutionPolicy cmdlet

Set-ExecutionPolicy RemoteSigned

By doing so your importExcel script should work.

Answer 3

I use C# and OpenXML library. I think it comes from MS, I don't remember details, it should be available for download and install. The C# code is the following:

internal static void ConvertXlsxToCsv(string SourceXlsxName, string DestinationCsvDirectory, string sheetName = null)
        {
            try
            {
                using (SpreadsheetDocument document = SpreadsheetDocument.Open(SourceXlsxName, false))
                {
                    foreach (Sheet _Sheet in document.WorkbookPart.Workbook.Descendants<Sheet>())
                    {
                        WorksheetPart _WorksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(_Sheet.Id);
                        Worksheet _Worksheet = _WorksheetPart.Worksheet;
                        SharedStringTablePart _SharedStringTablePart = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                        SharedStringItem[] _SharedStringItem = _SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ToArray();
                        if (string.IsNullOrEmpty(DestinationCsvDirectory))
                            DestinationCsvDirectory = Path.GetDirectoryName(SourceXlsxName);
                        bool convertSheet = false;
                        string _sheetName = _Sheet.Name;
                        string newFilename = string.Empty;
                        if (!string.IsNullOrWhiteSpace(sheetName))
                        {
                            if (_sheetName.Contains(sheetName, StringComparison.InvariantCultureIgnoreCase))
                            {
                                convertSheet = true;
                                newFilename = string.Format("{0}.csv", Path.GetFileNameWithoutExtension(SourceXlsxName));
                            }
                        }
                        else
                        {
                            convertSheet = true;
                            newFilename = string.Format("{0}_{1}.csv", Path.GetFileNameWithoutExtension(SourceXlsxName), _sheetName);
                        }
                        if (convertSheet)
                        {
                            newFilename = Path.Combine(DestinationCsvDirectory, newFilename);
                            using (var outputFile = File.CreateText(newFilename))
                            {
                                foreach (var row in _Worksheet.Descendants<Row>())
                                {
                                    StringBuilder _StringBuilder = new StringBuilder();
                                    foreach (Cell _Cell in row)
                                    {
                                        string Value = string.Empty;
                                        if (_Cell.CellValue != null)
                                        {
                                            if (_Cell.DataType != null )
                                            {
                                                switch (_Cell.DataType.Value)
                                                {
                                                    case CellValues.SharedString:
                                                        Value = _SharedStringItem[int.Parse(_Cell.CellValue.Text)].InnerText;
                                                        break;
                                                    case CellValues.Date:
                                                        Value = DateTime.FromOADate(double.Parse(_Cell.CellValue.Text)).ToString("yyyyMMdd"); 
                                                        break;
                                                    default:
                                                        Value = _Cell.CellValue.Text;
                                                        break;
                                                }
                                                
                                            }
                                            else
                                                Value = _Cell.CellValue.Text;
                                        }
                                        _StringBuilder.Append(string.Format("{0},", "\"" + Value.Trim()+ "\""));
                                    }
                                    var result = _StringBuilder.ToString();
                                    result = result.Substring(0, result.Length - 1); //remove last comma
                                    outputFile.WriteLine(result);                                   
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }