Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# Excel
I have created an application which converts Excel files to Access databases. During conversion the ":" symbol in data at the MACAddress column need to be replaced by a space.

I tried to modify the query by using replace method. But it didn't workout. Given below is the query that currently use:
cmd.CommandText = "INSERT INTO [MS Access;Database=" + Access + "].[NMS_List_Export] SELECT [IP Address] as [IPAddress],[MAC Address] as [MACAddress],[Last seen on Channel] as [LastseenonChannel] FROM [NMS_List_Export$]";
Can somebody please help me on this issue. thanks in advance!!
Posted 28-Dec-11 5:32am
Edited 28-Dec-11 5:46am
Mehdi Gholam253.3K
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

First, you need to retrieve the data from the spreadsheet - one row at a time. Once you've retrieved the data, THEN you manipulate it as desired. After that, you can insert it into your table. The short version is that you have to break your process into steps so that you can actually control what's going on.
 
Go forth, and code.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

In this query, in the Select part, INSERT INTO [MS Access;Database=" + Access + "].[NMS_List_Export] SELECT [IP Address] as [IPAddress],[MAC Address] as [MACAddress],[Last seen on Channel] as [LastseenonChannel] FROM [NMS_List_Export$]", use the Replace keyword on the MACAddress.
e.g. [IPAddress], Replace([Mac Address],':',' ') as [MACAddress]
 
You can read more about the Replace command here[^].
  Permalink  
Comments
hansika attanayake at 1-Jan-12 6:48am
   
hi, thank u for the reply. i tried using the replace function but it shows an error message called undefined function "replace". do i have to call a library in order to use the function?
Abhinav S at 1-Jan-12 6:54am
   
The Replace method in SQL Server is explained at http://msdn.microsoft.com/en-us/library/ms186862.aspx.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 250
1 Jochen Arndt 155
2 PIEBALDconsult 150
3 DamithSL 125
4 Afzaal Ahmad Zeeshan 120
0 OriginalGriff 5,790
1 DamithSL 4,601
2 Maciej Los 4,012
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,195


Advertise | Privacy | Mobile
Web02 | 2.8.141220.1 | Last Updated 28 Dec 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100