Highline Excel Class 23: Import Different Types of Data

Hello,

From a beginner ......

Problem: Unable to save monthly data from excel spreadsheet(2007) to oracle database(10g) with data types/cell formats. eg: if its a numeric value, the code should recognize as numeric value for the cell and save it in database as a numeric value in the numeric field of the database table. If not, the value should be saved as a text value in the database in the text field of the database table. This should recognize for each cell in the spreadsheet (say for 5 columns where number of rows will depend on the number of days for the month which i have figured out but the data type problem remains)

I am able to do this for a single date but unfortunately not for the entire month.

Any ideas or help will be of great help.

Using ADODB for connecting to DB.
Using macros for the purpose of reading and saving data for the month using ms dtpicker function for dates/month and save/read buttons for saving and reading data.

This is driving me crazy.

I am using named ranges in Excel to query from Visual Basic. Here is the SELECT staement that is giving me trouble:

.CommandText = "SELECT * FROM MODELS WHERE MODEL = '" & cboModel.Text & "'"

I get 'Data type mismatch in criteria expression.'

The MODELS in Excel are all numeric. When I check their types I get numeric. I have tried changing the cell format to text but TYPE still says they are numeric. I can add a ' to the front of the number and the type changes to text. But I still get the data type mismatch error. I assume the ' is the problem there.

CboModel.Text is a string. I have tried converting to integer, single, double, etc. with no solution.

So here's the question. If you type an integer into a cell in Excel what data type is it? How is it interpreted in a SELECT statement?

Hi all,

I'm dealing with a client who recieves an email subscription via SQL Server (2008) Reporting Services, giving him a daily Excel spreadsheet. The four columns should be of data type integer, integer, date, integer - the SQL code used in the report is correctly generating this output. However, when he subsequently attempts to import his daily spreadsheet into an Access DB for his own use, his tables are creating with types number (but in double format, which he is having to manually change to "long integer") and the date column is coming through as text. Something's clearly being lost in translation, but I'm unsure if it's the export from SRS to Excel, or the import from Excel to Access, or both.

Any advice on the text box formatting in SRS to help me get to the end result of long integer and date in Access would be very much appreciated. TIA.

I am trying to import data from another Excel file, but when I choose the file, the Data Link Properties appears instead of Select Table.

I believe it is on a shared network; that is the only different item.

I am a novice when it comes to any external data import or data query, so please be as detailed as you can.

How do you mach the data type in excell with the data type in access? In
excess I have have for SSN the category as special and type social security
number, ans in Access I have the data type as" number". What can I do to
get them to mach so I can update my records? Also with Zip code and Phone
number in excel and "number" in Access.

Andrew

i am trying to import data from many websites into excel.
the problem involves football teams and how many corners they took in each match. (don't ask!)

each webage has a predictable pattern for the address, so i have worked out that only a number changes in the address by 1 to get the next page or match.

eg

http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140

is the first match.
i am interested in gathering the two team names
SC Heerenveen
Excelsior

and the number of corners each ("Hoekschoppen")
4
5

i am trying to get these 4 bits of data into a row in excel, then underneath data from the next match:

http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140

the number in the middle of the address decreases by 1 each time, so the first few would be

http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140

is there a way i can automate this process?
(i have a list of all 306 addresses / matches in excel)

any help / hints appreciated

thanks in advance.

Is there a VB command that'll tell you whether a certain cell contains a date?

I thought of using number format, but I can't always guarantee the dates will be in the same format. Therefore I'm tempted to check to see if the numberformat contains "YY". I think it'll work, but I guess the actual question is, is there a more formal way to query the data type of a given cell?

Hi,

I have a problem concerning datatypes for an if-loop, the failure message tells me "Datatypes incompatible" for this code-line:

Code:

If CLng(.Worksheets("Database").Cells("R" & last)) >= CW Then

Here is also the full context:

Code:

CW = CLng(InputBox("Please enter the current week (1-53)", Week))
With ActiveWorkbook
last = .Worksheets("Database").Cells(Rows.Count, 18).End(xlUp).Row
i = 0
    For u = 7 To last
        
        If CLng(.Worksheets("Database").Cells("R" & last)) >= CW Then
            .Worksheets("Database").Rows("last").Copy Destination:=.Worksheets(1).Rows("3+i")
        End If
    Next u
    End With

Thanks in advance for your help!

Hi

Is there a way to get the datatypes of each field in a table using sql (Using an ODBC connection to connect to SQL Server 2005.

Basically I want a query that returns the filed names and the data types.

Thanks

Richard

I have a propietary database query application which keeps exporting my queries into text. When I import these files into access as a table it presents problems. I need to convert these data types to numbers so that I can perform sum calculations on them.

Is there a way to do this in the query section of access? If not, Is there a way to specifically tell access that I want specific columns from a specific table to change from a text data type to a number data type upon import?