0

I have a large XML file that I open. I use VBA to loop through each line and do some boring editing and then write to a table. The French characters get messed up. Another example:

SCT DIVERSITÉ becomes SCT DIVERSITÉ

Open "BIGLIST.XML" For Input As #1

Do Until EOF(1)
   Line Input #1, ReadData 
   XMLLine = Replace(LTrim(RTrim(ReadData)), "'", "''") ' Using Replace to deal with single quotes

' A whole bunch of data transformation takes place

    DoCmd.RunSQL "INSERT INTO [TableName] (Field Names) VALUES (All the boring variables I make)"
Loop
Close #1
user3101112
  • 31
  • 1
  • 3
  • Unless your computer's codepage for non-Unicode programs matches the encoding of that XML file, you [cannot use `Line Input`](https://stackoverflow.com/a/23980044/11683) on it. Why don't you properly load it with MSXML? On a side note, judging by the `Replace`, you need to have a look at https://stackoverflow.com/q/332365/11683 too. – GSerg Jul 07 '21 at 16:09
  • The Access import of this particularly horrible XML file chopped it into disconnected tables with no way to connect them with certainty. (i.e. no PKs or FKs). Therefore I was doing it a more difficult way that allowed me to import just the pieces I need. – user3101112 Jul 07 '21 at 16:34
  • Do not use the Access import. Use the MSXML directly. – GSerg Jul 07 '21 at 17:33
  • Don't read XML as if it were plain text. Always use a real XML parser, which knows how to recognise the encoding of the file. – Michael Kay Jul 08 '21 at 07:11
  • "Therefore I was doing it a more difficult way that allowed me to import just the pieces I need." - An optimisation that does things wrong is not an optimisation, it's a bug. – Michael Kay Jul 08 '21 at 07:12
  • I have never used the MSXML parser and with search I can't even figure out how to load it. Suggestions on XML parsers that might be at my low intellectual level? – user3101112 Jul 08 '21 at 18:10

0 Answers0