Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Posts
    4

    Question Unanswered: Newbie - Data Conversion?

    Hi everyone... sorry, but I'm really new at this! I'm trying to make a table out of an imported Excel file (which is automatically generated as an all-text data type). I wrote a query that takes this excel file and puts it into the format we need (i.e., appropriate column headers, removing "garbage" characters and spaces). Is there any way to format the data going into the table by a specified datatype?

    Sorry if this is simplistic or wasting your time, but I'm really stumped . However, keep in mind that I started learning SQL this morning and I've already gotten this far .

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How did you get EXCEL in to a table?

    DTS, I imagine.

    I would have it set up to a staging table where all the columns are varchar..then you can do whatever you want...

    what code did you write?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2004
    Posts
    4
    Sorry I didn't make it clear . I import the Excel file into my Access database, and manipulate it from there. The Query-Wizard was pretty retarded, so I decided to just do the SQL myself.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    INSERT...
    SELECT cast(<source_field1> as <data_type>)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jun 2004
    Posts
    4
    I thought I would include a snippet of my crappy code . YES, I realize you typically only have to put brackets with buzzwords and spaces, but it's just something I do.

    Where exactly would I put the CAST() command at?

    SELECT [Sheet1].[Field15] AS Location, [Sheet1].[Field2] AS EquipID, [Sheet1].[Field3] AS Asset, [Sheet1].[Field4] AS [Date], [Sheet1].[Field5] AS Eracent, [Sheet1].[Field6] AS [Login ID], [Sheet1].[Field7] AS RAM, [Sheet1].[Field8] AS [O/S], [Sheet1].[Field9] AS CPU, [Sheet1].[Field10] AS [EMP ID], [Sheet1].[Field11] AS [Cost Center], [Sheet1].[Field12] AS [First Name], [Sheet1].[Field13] AS [Last Name], [Sheet1].[Field14] AS Phone, [Sheet1].[Field16] AS Div, [Sheet1].[Field17] AS Dept INTO FormattedReport
    FROM Sheet1;

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    SELECT cast([Sheet1].[Field15] as varchar(35)) AS Location, ... , etc.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jun 2004
    Posts
    4
    Errrggg! I got a "Missing Operator" syntax error with the first CAST()! Did I type something incorrectly?

    SELECT cast([Sheet1].[Field15] as VARCHAR(35)) AS Location, cast([Sheet1].[Field2] as INT) AS EquipID, cast([Sheet1].[Field3] as INT) AS Asset, (so on and so forth)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •