SQL syntax - defining the format of a field

[lovallee]

Hello,

I use ADOBD.Connexion / Microsoft Jet SQL to connect to Excel tables and create a pivot cache.

I need to understand the required SQL syntax to define a field format.

Exemple:

SELECT Table1.Field1 FROM Table1

And I need Table1.Field1 to be treated as text

How can I set the format of a field in my SQL statement?

Thanks!
Louis

 

[Mark W.]

What happens when you use...

SELECT Table1.Field1 & '' FROM Table1

Note: Using 2 single quotes to represent a null string; thus, coercing the numeric value into text.

 

[lovallee]

Thanks Mark,

I will give it a try.

In the mean time I found on the Microsoft web site that when ADOBD connect to Excel, it scans the first 8 rows to detect the field type (text, num, date or mixed). I arranged my data to ensure ADOBD detect the correct data type for each field.

Also very useful is the addition of "IMEX=1" in the ConnectionString

 .ConnectionString = "Data Source=" & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & ";" & _
"Extended Properties=""Excel 8.0;IMEX=1;"""

The IMEX=1 is necessary to allow mixed data type fields to return both text and numeric data as text.

Louis

 

[ac3100]

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strFilePath & ";" & "Extended Properties=""text;HDR=Yes;IMEX=1;FMT=Delimited"""


I am having a problem with mixed data types in a textfile. I tried the IMEX=1 thing(as you can see), but it still dropped non-numeric values in the first column.

The first column has customer ID values. Here is an example:
1200
1689
32654
207-589-6622
65231
207-589-6323
54878


On my spreadsheet where the information goes, only numeric information appears.

1200
1689
32654

65231

54878

 

[lovallee]

AC3100

When connecting to an Excel table, ADOBD auto-detect the field type by looking at the first 8 values in that field. Ensure that in the first 8 values of that field, you have at least 1 numeric value and 1 text value. ADOBD should then assume it is a "mixed" type field and keep all values.

Please try and let us know.

 

[ac3100]

The way that the textfile is produced cannot be changed. In this case there are at least 8 that are all numeric in the first field. Thereffore it is omitting any non-numeric data. I find it interesting that it only goes to 8 rows.

I think I am going to have to read the text in line by line and use logic to find commas, enter the data and advance 1 column and repeat. I am not sure how much longer it will take.

I will search for importing text into excel that way unless there are any better ideas

 

[ac3100]

What happens when you use...

SELECT Table1.Field1 & '' FROM Table1

Note: Using 2 single quotes to represent a null string; thus, coercing the numeric value into text.

Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")
strFilePath = oFSObj.GetFile(strFullPath).ParentFolder.path
strFilename = oFSObj.GetFile(strFullPath).Name

Set oConn = CreateObject("ADODB.CONNECTION")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strFilePath & ";" & "Extended Properties=""text;HDR=YES;IMEX=1;FMT=Delimited"""

Set oRS = CreateObject("ADODB.RECORDSET")

strSQL = "SELECT * FROM " & strFilename '& "
oRS.Open strSQL, oConn, adOpenStatic, adLockOptimistic, adCmdText

While Not oRS.EOF
Sheets(3).Range("A1").CopyFromRecordset oRS, 65536
Wend

oRS.Close
oConn.Close

*****Is there anything I can do to change my strSQL statement so that it will coerce the numeric values into string values in the first column or every column for that matter.*****

The text file has 37 columns. The first column is the on that is dropping the text data but keeps the numeric. If I can convert the Numeric data to text then I should be able to capture everything.

Please help!

 

[lovallee]

*****Is there anything I can do to change my strSQL statement so that it will coerce the numeric values into string values in the first column or every column for that matter.*****

No, a SELECT statement cannot change the data type. Fields data type are defined by ADOBD before the SQL statement is run.

In your situation, I would rather manipulate the Excel file before the connexion is open.

2 options (hopefully one of them will work):

1st option) Select your mixed type field, change the format of the column to"text", then select the column again, go in menu Data>Text to column, follow the wizard steps and choose paste as "Text".

2nd option) Add the single quote ' character as the first character in each cell of your mixed type field. When the content of a cell starts with ', Excel marks the cell content as "Text".

Let us know if it worked this time.

 

[Mark W.]

To change a numeric value into a text value consider using...

SELECT Trim(Str(A.Number))
FROM List A

 

[lovallee]

To change a numeric value into a text value consider using...

SELECT Trim(Str(A.Number))
FROM List A

Mark,

SQL does not accept Excel's functions.
SQL (called within VBA) accepts VBA functions

When SQL connects to Excel tables, you cannot expect SQL to convert data. Data must be converted/pre-arranged in Excel first.

Louis