Hello,

I would like to ask how it is possible to make a query in MSQuery that can
retrieve all format of data from an Excel file, ie:

In a spreadsheet I have the following data:
A                         B      C
1 New Worksheet
2 234                   23     April
3 Apple                32     May
4 2005.12.30        31     23

In the query, I would like to see the same data, but if I tried, in one
field I could only query data if theye were in the same format, ie. if I make
a query on the above data:

SELECT Sheet$.A, Sheet$.B, Sheet$.C
FROM Excel.xls\Sheet$ Sheet$

Then I got the result:

A                         B        C
1 New Worksheet
2                                  April
3 Apple               32       May
4                        31

How can I make a query in that I can retrieve all data type in all fields (i
got the same table as the first above)?

Thanks for your help in advance

Have you tried "converting" (i.e. formatting) data in Excel as Text?


ML

---
http://milambda.blogspot.com/

I have thought about this, but I would not like setting the formats in the
Excel files I work with (so as to be able to use them properly in the future,
too).

„ML” ezt írta:

Show quoteHide quote

I'm not suggesting this as a permanent solution, but rather to *try* it.


ML

---
http://milambda.blogspot.com/

This will truly work if I try, thank You for Your suggestion. But it is not a
salvation for my problem.
During my work I simply do not have any time to set the format of Excel
sheets. There is sometimes only time for save Excel files to my computer, and
then I have to process the data immediately.
That is what I woud like to do with a program, for which I need (if it is
possible) the above described data query.

Could You please give me advice, how can I find information - solution on
this / or to whom may I turn to for further help?

Thanks in advance

pontitt

„ML” ezt írta:

Show quoteHide quote

Books Online also suggest naming ranges on the Excel Worksheet.

I, personally, prefer exporting data from Excel to XML or a comma-separated
text file before importing it into SQL. Would any of these two methods suit
you?

1) XML (MS Excel 2003):
  - design an XML Schema (once);
  - map the Schema to your data (once for each file);
  - export data to XML (every time data is changed).

2) CSV:
  - create a SCHEMA.INI file to describe the structure of data (look it up
on MSDN);
  - create a linked server to the file location or use OPENQUERY;
  - save Excel data as CSV.


ML

---
http://milambda.blogspot.com/

random select a row based a weight column.
Timeout expire
Optimize this query
How can I insert a new field between existing field?
store results over network