Question

Excel as db - ' symbol is added to the inserted cells

  • 8 December 2022
  • 9 replies
  • 172 views
C
Badge +4

Hi,

While inserting data to a sheet some of the cell values having a single quote symbol -‘- added to the inserted data. So, how I can avoid that.

And can we do anything with the connection string to avoid the data formats while picking data from one shet to another? 

Note: Here the data to be inserted is coming from another sheet whic is extracted from SAP application.

9 replies

Padmakumar
Userlevel 7
Badge +13

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.

Padmakumar
Tamil Arasu10
Rank
Userlevel 5
Badge +11

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!

 

_Tamil_
C
Badge +4

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.

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

 

Insert Query:

INSERT INTO [Revenue Register$$A2:BA3500] ([Profit Center], [Billing Doc], [Billing Reference], [Billing Date], [Item], [Material], [Description], [Billed qty], [Basic Invoice Value], [Delivery Doc Item], [Sales Document], [Customer Payer], [Customer name  Payer], [Ship to Party], [Address Ship to Party], [Delivery Doc NO], [Incoterms], [Transporter Name], [Vehicle number], [Amt in Doc curr], [Billing doc currency], [Control Code], [Sales 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 [Revenue Register$A2:BA3500] ([Profit Center], [Billing Doc], [Billing Reference], [Billing Date], [Item], [Material], [Description], [Billed qty], [Basic Invoice Value], [Delivery Doc Item], [Sales Document], [Customer Payer], [Customer name  Payer], [Ship to Party], [Address Ship to Party], [Delivery Doc NO], [Incoterms], [Transporter Name], [Vehicle number], [Amt in Doc curr], [Billing doc currency], [Control Code], [Sales Office], [GL]) 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...

Paul Hawkins
Rank
Userlevel 2
Badge +7

@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?

 

C
Badge +4

@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?

Padmakumar
Userlevel 7
Badge +13

@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 [WorkingSheet$$] values ('$OracleDatasetRow-1[0]$','$StDate$',$OracleDatasetRow-1[3]$,'$StDate$','$ReferenceData$',$SQLDatasetRow[4]$,$SQLDatasetRow[5]$,$SQLDatasetRow[6]$,$SQLDatasetRow[7]$,$SQLDatasetRow[8]$,$SQLDatasetRow[9]$,$SQLDatasetRow[10]$);

 

Output

 

Padmakumar
C
Badge +4

@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 [WorkingSheet$$] values ('$OracleDatasetRow-1[0]$','$StDate$',$OracleDatasetRow-1[3]$,'$StDate$','$ReferenceData$',$SQLDatasetRow[4]$,$SQLDatasetRow[5]$,$SQLDatasetRow[6]$,$SQLDatasetRow[7]$,$SQLDatasetRow[8]$,$SQLDatasetRow[9]$,$SQLDatasetRow[10]$);

 

Output

 

Thanks for writing in detail.

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

Padmakumar
Userlevel 7
Badge +13

@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 [WorkingSheet$$] values ('$OracleDatasetRow-1[0]$','$StDate$',$OracleDatasetRow-1[3]$,'$StDate$','$ReferenceData$',$SQLDatasetRow[4]$,$SQLDatasetRow[5]$,$SQLDatasetRow[6]$,$SQLDatasetRow[7]$,$SQLDatasetRow[8]$,$SQLDatasetRow[9]$,$SQLDatasetRow[10]$);

 

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...

Padmakumar
C
Badge +4

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.

Reply


Terms & ConditionsCookie settings