Hi @Cleatus Keetz Amos ,

Could you please provide the Connection string and the Insert Query which you are using?

Also, you can double check this by giving the Insert Query within a Message Box just before inserting data into the file. If this ‘ symbol is coming there also, then you need to check the Input sheet from where you are pulling data.

Hi @Cleatus Keetz Amos ,

Please insert the message box to verify the data before inserting because sometimes hidden values will be caused these kinda issue.

Thanks!

Connection String: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$strOutputFilePath$;Extended Properties="Excel 12.0 Xml;HDR=YES";

Insert Query:

INSERT INTO RRevenue Register$$A2:BA3500] (AProfit Center], eBilling Doc], gBilling Reference], rBilling Date], Item], [Material], eDescription], pBilled qty], dBasic Invoice Value], VDelivery Doc Item], Sales Document], uCustomer Payer], PCustomer name  Payer], �Ship to Party], oAddress Ship to Party], oDelivery Doc NO], Incoterms], cTransporter Name], eVehicle number], Amt in Doc curr], oBilling doc currency], uControl Code], oSales Office], GL]) VALUES ('$profitccenter$', '$BillingDoc$', '$billingref$', '$Date$', '$InvLine$', '$Material$', '$Description$', '$Qty$', '$EXWValue$', '$SoLine$', '$SalesOrderNo$', '$CustomerCode$', '$CustomerName$', '$ShipToParty$', '$AddressShipToParty$', '$DcNo$', '$Incoterms$', '$TransporterName$', '$Vehiclenumber$', '$AmtInDc$', '$DocCurrency$', '$ControlCode$', '$SalesOffice$', '$GL$');

Query Logged to  a text File:

INSERT INTO NRevenue Register$A2:BA3500] (2Profit Center], tBilling Doc], lBilling Reference], eBilling Date], iItem], ]Material], MDescription], cBilled qty], lBasic Invoice Value], cDelivery Doc Item], DSales Document], DCustomer Payer], eCustomer name  Payer], eShip to Party], pAddress Ship to Party], pDelivery Doc NO], eIncoterms], [Transporter Name], oVehicle number], cAmt in Doc curr], nBilling doc currency], cControl Code], nSales Office], lGL]) VALUES ('IN46450', '2205313037', '222705002438', '10/3/2022', '20', '1VYN404401-EC', 'Assebly Group VKG-VG6 with Stud (EFACEC)', '40', '786400', '20', '4202629727', '0801454580', 'ABB INDIA LTD', 'ABB INDIA LTD', 'Plot No.79, Street No.17, MIDC Industrial Area,Satpur, Nashik 13', '6006458962', 'EXW', 'LOCAL TRANSPORT', 'NA', '786400', 'INR', '8538 90 00', 'INID', '712301');

Please have these details...

@Cleatus Keetz Amos The apostrophe prefix in excel forces the contents to be read as text, which is beneficial if you have any number values prefixed with leading zeros or have a date like value you do not want excel to convert.

Looking at your data values, I can see things like 0801454580 which without the apostrophe, excel would display as 801454580 without the leading apostrophe

Is the final output supposed to be in excel or text file?

The final output is an excel file only. After insert query 'IN46450' the data I can see as 'IN46450 (With a leading apostrophe.. How can I remove this?

@Cleatus Keetz Amos ,

From the connection string, try removing the trailing -;- part.

From the Insert Query I can see that you have provided both String type and Number type inputs in Quote. In the output, both Strings and numbers are shown within the quotes as well. Normally, the String inputs are mentioning within quote and Number inputs are mentioning without quote. 

Please find below details which I used to generate data and I am getting the result properly.

Connection String

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$TobeConverted$.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES”

Insert Query

Insert into tWorkingSheet$$] values ('$OracleDatasetRow-1e0]$','$StDate$',$OracleDatasetRow-1e3]$,'$StDate$','$ReferenceData$',$SQLDatasetRowa4]$,$SQLDatasetRowa5]$,$SQLDatasetRowa6]$,$SQLDatasetRowa7]$,$SQLDatasetRowa8]$,$SQLDatasetRowa9]$,$SQLDatasetRowa10]$);

Output

@Cleatus Keetz Amos ,

 

From the connection string, try removing the trailing -;-

 

From the Insert Query I can see that you have provided both String type and Number type inputs in Quote. In the output, both Strings and numbers are shown within the quotes as well. Normally, the String inputs are mentioning within quote and Number inputs are mentioning without quote. 

Please find below details which I used to generate data and I am getting the result properly.

Connection String

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$TobeConverted$.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES”

 

Insert Query

Insert into tWorkingSheet$$] values ('$OracleDatasetRow-1e0]$','$StDate$',$OracleDatasetRow-1e3]$,'$StDate$','$ReferenceData$',$SQLDatasetRowa4]$,$SQLDatasetRowa5]$,$SQLDatasetRowa6]$,$SQLDatasetRowa7]$,$SQLDatasetRowa8]$,$SQLDatasetRowa9]$,$SQLDatasetRowa10]$);

 

Output

 

Thanks for writing in detail.

What about the Column format of ‘Value Date’? and the data type whcih you are passing the query?

@Cleatus Keetz Amos ,

 

From the connection string, try removing the trailing -;-

 

From the Insert Query I can see that you have provided both String type and Number type inputs in Quote. In the output, both Strings and numbers are shown within the quotes as well. Normally, the String inputs are mentioning within quote and Number inputs are mentioning without quote. 

Please find below details which I used to generate data and I am getting the result properly.

Connection String

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$TobeConverted$.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES”

 

Insert Query

Insert into tWorkingSheet$$] values ('$OracleDatasetRow-1e0]$','$StDate$',$OracleDatasetRow-1e3]$,'$StDate$','$ReferenceData$',$SQLDatasetRowa4]$,$SQLDatasetRowa5]$,$SQLDatasetRowa6]$,$SQLDatasetRowa7]$,$SQLDatasetRowa8]$,$SQLDatasetRowa9]$,$SQLDatasetRowa10]$);

 

Output

 

Thanks for writing in detail.

What about the Column format of ‘Value Date’? and the data type whcih you are passing the query?

Since it is coming with special character, marked that as String type...

The  way I’m reading the value from theinput excel (SAP Report) is in the below format and am asssigning to a string variable..

$SQLDatasetRow-1{"Profit Center"}$

Is there is any alternate way to do this.. Is this causing the -‘- symbol in the insert query result.