SQL copies data with indent (1 Viewer)

[murray83]

Hi All

have exhasusted AI and google help so thought would actually ask some proper smart people ( me not included )

so i have the following SQL

INSERT INTO tblForCSV_main ( OrderNo, ProductCode, Qty )
SELECT Mid([RawData], InStr([RawData], "Booking Ref:") + 12, 12) AS BookingRef, Mid([RawData], InStr([RawData], "Pallets") + 21, 13) AS HSProductCode, Mid([RawData], InStr([RawData], "Pallets") + 68, 5) AS Qty
FROM TempRawData
WHERE TempRawData.RawData IS NOT NULL;

which for the most copies it fine apart from the OrderNo as it shows up like this in the tbl

am at a loss to why, but hope someone here will hold the key/secret

 

[plog]

Most likely this is because that's what's in the data. Pretty confident it has nothing to do with your SQL.

This probably gets fixed by putting a Trim() around each field of the SELECT.

 

[GPGeorge]

Hi All

have exhasusted AI and google help so thought would actually ask some proper smart people ( me not included )

so i have the following SQL

INSERT INTO tblForCSV_main ( OrderNo, ProductCode, Qty )
SELECT Mid([RawData], InStr([RawData], "Booking Ref:") + 12, 12) AS BookingRef, Mid([RawData], InStr([RawData], "Pallets") + 21, 13) AS HSProductCode, Mid([RawData], InStr([RawData], "Pallets") + 68, 5) AS Qty
FROM TempRawData
WHERE TempRawData.RawData IS NOT NULL;

which for the most copies it fine apart from the OrderNo as it shows up like this in the tbl

View attachment 120251

am at a loss to why, but hope someone here will hold the key/secret

Where is that "TempRawData" coming from? That would appear to be the source of the problem. It looks like a hidden line break character in that source text. I don't think it would be an indent, or tab character.

You can probably resolve the immediate problem by "sanitizing" the input from that TempRawData source.

For example, you could try using replace to remove the unwanted line break character. But a thorough solution might need to account for other such non-printing characters.

 

[Gasman]

Check the the length?

 

[jdraw]

murray83,
Crystal Long (strive4peace) provided a free routine to write the ascii values represented by characters in a string. If you use it to "display" the characters in your raw data, you can likely determine "strange characters causing your concern".
Let us know the result.

 

[murray83]

Cheers all. Not back in work till Friday so will give it all a look then

But cheers