Import excel file with bad column order

I’ve to import a file (probably .csv or .txt) via a SSIS-package. The problem is, that the file is an excel file at the moment and is written very human readable which means it looks something like this: Name1; ColumnName1; Data; Data; Data;… Name1; ColumnName2; Data; Data; Data;… Name1; ColumnName3; Data; Data; Data;… Name2; ColumnName1; […]

Why is wrapping inconsistent in C# Excel Interop?

Using this code: var avgOrderAmountHeaderCell = (Range)_xlSheetDelPerf.Cells[DEL_PERF_COLUMN_HEADER_ROW, AVG_ORDER_AMOUNT_COLUMN]; avgOrderAmountHeaderCell.Value2 = String.Format(“Avg Order{0}Amount”, Environment.NewLine); avgOrderAmountHeaderCell.WrapText = true; var avgPackageCountHeaderCell = (Range)_xlSheetDelPerf.Cells[DEL_PERF_COLUMN_HEADER_ROW, AVG_PACKAGE_AMOUNT_COLUMN]; avgPackageCountHeaderCell.Value2 = String.Format(“Avg Package{0}Count”, Environment.NewLine); avgPackageCountHeaderCell.WrapText = true; …I get this result (column header on left looks fine, while the one on the right has an “empty line” between the second and third wrapped […]

Select on .csv file stops working after columns 1 and 2

I have the following line in a .csv file: 1234;Einkauf;26.08.2015;137873;616005;50308;Hiag;4100000;Place Rd. 8, 9999 Ort, HST;;;Test Place Rd. Ort;2000;VSM80;185.05;12;155.55;0.02 I run the following code: Set cn = CreateObject(“ADODB.Connection”) Set rs = CreateObject(“ADODB.Recordset”) cn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & strPath & “;Extended Properties=””text;HDR=Yes;FMT=Delimited””;” sQuery = vbNullString sQuery = sQuery & “SELECT * FROM ” & strFileName sQuery = sQuery […]

Vlookup 2 column index numbers

I need to look in 2 index value, if there is no value present in that index, it should check the next index number and pop the value. I tried these 2 formulas, its not working : =IFERROR(VLOOKUP(A5,’sheet1′!A:S,**19:20**,FALSE),””) =IF(NOT(ISNA(VLOOKUP($A5,’Sheet1′!A1:T92,19,0))),”Main”, IF(NOT(ISNA(VLOOKUP(A5,’Sheet1′!A1:T92,20,0))),”Residual”,”Notfound”)) Here i need to pop up S, in case if column S is blank take […]

SQL Case in Excel

I would like to compare a column based on range of value, it the value matches the range then it should be 1 else 0. For example: Column Name – Score: contains value ranging from 1 – 10 Now the conditions :- Column Name – Poor Grade: if Value of Score between 1-3, then 1 […]

Need to add diagonal cells with an additional cell for each subsequent diagonal

I need help getting the sum of specific cells in a diagonal format. My issue is that each subsequent diagonal grows by 1 cell. I was thinking I use a For loop, but I don’t know how to add an additional cell to the summation for each iteration of the loop. The picture below shows […]

format an excel column to accept date in particular format only phpExcel

I am generating an excel sheet via phpExcel in which dates are retrieved in 01-JAN-1900 format.But I want it to be visible in DD-MM-YYYY format right from when the excel sheet is generated with default date values fetched from DB.Also the date format is DD-MM-YYYY in DB.So how can we format that cell to display […]

Converting CSV to XLSX using openpyxl, but quote comes infront of numbers

I tried to convert CSV to xlsx using openpyxl. The conversion was successful but i noticed quote (‘) in-front of numbers. Making it string instead of numbers. As I am new to python you help would be grateful. Here is code I am using: import os import glob import csv import openpyxl for csvfile in […]

Is it possible to automatically pre populate a column of excel

Number——Automatic repeat of numbers 87638 10898 197981 339831 3809801 Hi guys, I am fresh to excel I am usually doing back end programming but for my work placement I am being asked to pre populate columns from curtains which is the system this Credit Union uses to store client details, I am being asked to […]

Code to Combine Sheets Does Not Finish Combining

The code below is intended to take the data in columns B:N from several spreadsheets and combine that data into one sheet and then make a table from it. Column A gets the file name from the source files. The columns are titled as follows: Site/Scan Name Plugin ID CVE CVSS Risk Host Protocol Port […]

MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.