locked
XML import: how to get rid of the namespace colon in column names RRS feed

  • Question

  • Hi

    I use excel to import xml files.  As a result I get colons in the column headings.  This causes problems when the data is handled later.

    I aim to the data flow as simple as possible and so am not keen on altering headers to remove colons but would do this if there was a recommended way of dealing with these colons. Best practice naming conventions is not to include punctuation in field names but with xml colons seem inevitable.

    Is there a way to remove to colon?  Are there any other suggestions on how to avoid such problems?

    FYI one problem is when these excel sheets get inspected in Access which treats the colon as an SQL "AS".

    Tuesday, August 4, 2015 10:44 AM

Answers

  • Hi Gyrator,

    I have a small routine I use to strip invalid characters form a CSV that I import. It processes the whole file replacing all occurrences then saves the result, before the actual import occurs. I have altered to just strip out colons...

    Better if you can remove them at source?

      Dim strFileName As String, iFileNum As Integer, sBuf As String, sTemp As String, iAffected As Integer
    
     iFileNum = FreeFile
       Open strFileName For Input As iFileNum
          Do Until EOF(iFileNum)
             Line Input #iFileNum, sBuf
             sTemp = sTemp & sBuf & vbCrLf
          Loop
       Close iFileNum
       iFileNum = FreeFile
     
          sTemp = Replace(sTemp, ":", vbNullString)
           
         'now save'
          Open strFileName For Output As iFileNum
          Print #iFileNum, sTemp
          Close iFileNum
     '...Process Your import ...'


    Brian, ProcessIT- Hawke`s Bay, New Zealand

    Tuesday, August 4, 2015 9:12 PM